Press "Enter" to skip to content

Month: March 2016

Using The Default Trace

Published 2016-03-16 by Kevin Feasel

Jon Morisi shows how to use the default trace:

Often times while troubleshooting an issue, you’ll want more details than what you can find in the application log or SQL Log.  In the background, SQL Server runs a default trace which includes a lot of items to help with troubleshooting including (but not limited to) errors, warnings, and audit data.  I often run the following script as a quick way to find additional details for “ERROR” items from the default trace.

Jon notes that the default trace has been put on the deprecation list, so keep that in mind if you do use it.

Comments closed

Pulling Non-Clustered Index Data

Published 2016-03-15 by Kevin Feasel

Kenneth Fisher shows using a non-clustered index potentially to reconstruct corrupted data on a clustered index:

So why would you want to do this? Well lets say for example you have a table in a database where the clustered index has become corrupted. Let’s further say that no one mentioned this to you for .. say a year. (No judging!) So your only option at this point might be to use the REPAIR_ALLOW_DATA_LOSS of DBCC CHECKDB. But when you are done how much data has actually been lost? Can you get any of it back?

If you’ve lived a good life and are very lucky, you might recover all data this way.  Otherwise, it’s a good idea to run CHECKDB more frequently and check those backups regularly as well.

Comments closed

Identify Page Split Sources

Published 2016-03-15 by Kevin Feasel

Dennes Torres shows us how to figure out where bad page splits are occurring:

When one page becomes full and a new page is needed this is reported as a page split, but this is a regular operation with no bad consequences for our queries. The problem happens with updates and non-sequential inserts, when the row needs to be inserted in the middle of the pages of the object and there is no space for this. SQL Server creates a new page, transfers half of the page data to the new page and writes the row data. This creates page fragmentation and is very bad for performance and is also reported as page split.

We can find the bad page splits using the event sql_server.transaction_log. This event monitors all the activities in the transaction log, because that we need to use with caution. We can filter the ‘operation’ field looking for the value 11, which means LOP_DELETE_SPLIT. This is the deletion of rows that happens when SQL Server is moving rows from one page to another in a page split, a bad page split.

It’d be nice to be able to find the particular query causing the page split, and it’d also be nice to find a less resource-intensive method of displaying this information.

Comments closed

Documentation Survey

Published 2016-03-15 by Kevin Feasel

The SQL Server team is asking for your help:

The SQL Server documentation team is working to improve the SQL Server documentation in an effort to enable you be more successful with and gain more value from SQL Server. Please let us know your thoughts on how we can improve the documentation by taking this brief survey by March 18.

We appreciate and are listening to your feedback! Thank you.

The questions aren’t very onerous, so help out the documentation folks and fill it out.

Comments closed

Windows Authentication On Linux

Published 2016-03-15 by Kevin Feasel

Ryan Adams shows us how to get Active Directory authentication on Linux using Samba and PAM:

Back to our question at hand.  Since the preview just got released I have not had a chance to test this out with SQL Server.  However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM.  I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out.  Since I already have some written installation instructions, that’s what I am providing here.

Chrissy LeMaire, in the comments, shows how to do it in OpenSUSE.  She also has a brand new blog post on the topic.

Comments closed

Parameterizing Attunity Queries In SSIS

Published 2016-03-15 by Kevin Feasel

Melissa Coates shows us how to parameterize queries if you’re using the Attunity connector for Oracle in SSIS:

The Attunity connector for Oracle used inside of the SSIS data flow looks a little different than the typical OLE DB connector we most commonly use. Specifically, the Attunity source has two options: “Table Name” and “SQL command.” What the Attunity Oracle Source doesn’t have in this dialog box is “SQL command from variable” (like we see for an OLE DB source).

Expressions are your friend.

Comments closed

Filtering SSMS Tables

Published 2016-03-14 by Kevin Feasel

Jens Vestergaard shows us how to filter tables in Management Studio:

As a quick tip, this on is one of these tool tips, that just makes your everyday much easier. Sometimes, more than often, you run into databases that contains a huge number of tables – all listed alphabetically. This can, at times, be cumbersome and annoying to browse. SQL Server Management Studio (SSMS) actually has a feature that will assist you in getting your job done, quicker; It’s called Filtering.

My most common filter criterion is by schema, but there are several filtering options available.

Comments closed

Data Modeling In Power BI

Published 2016-03-14 by Kevin Feasel

Ginger Grant discusses creating models in Power BI:

When comparing the features of the Online Client with the Desktop version of Power BI, there is one very obvious difference, there is no way to create a data model in Power BI online. It is not possible to create a data model using the online client. The online client is designed to connect to an existing online source such as Sales Force or Azure DB. If you are using an existing model, there is no need to create one. When using the enterprise gateway, which uses an on-premises database such as a SQL Server, SSAS or Hana, the data model is contained within the database exposed via the enterprise gateway, so again no reason exists to create a data model. Report creation can occur either using the online client or desktop as there is compelling technical reason that I am aware of which would determine where the report is created.

Cf. an earlier post on Power Pivot and Power BI modeling.

Comments closed

Sys.Messages Error

Published 2016-03-14 by Kevin Feasel

Anup Warrier had an issue recently with sys.messages queries generating an error:

What you normally expect when you run SELECT * FROM sys.messages? The query will return a row for eachmessage_id or language_id of the error messages in the system, for both system-defined and user-defined messages.

Rather than returning the rows, system generated an error for me:

Msg 18058, Level 17, State 0, Line 1
Failed to load format string for error 362, language id 1033. Operating system error: 317(The system cannot find message text for message number 0x%1 in the message file for %2.). Check that the resource file matches SQL Server executable, and resource file in localized directory matches the file under English directory. Also check memory usage.

Anup follows up with the answer, so if you’re running into this issue, check out his post.

Comments closed