Paul Duffett – Me, myself and MS SQL Server

Hi.

Have you ever set off a profiler trace and then realised that you’ve amassed tons and tons of data and now you need to start searching for that needle in a haystack?

If the answer to this question is yes then you might be interested in a pretty cool SQL function that lets you read the trace files and, even better, import them into a table.

Consider the following SQL statement:

FROM ::fn_trace_gettable(‘D:\DBAScripts\output\sql_trace_file.trc’, default);

This will import your SQL trace files into a table, after which you can add indexes to optimise the numerous queries you will no doubt have to run against the data.

Let me just break down the syntax of that statement to try and give you a better understanding of how you can best utilise this feature.

The SELECT * INTO is straight-forward stuff so I won’t bother with that bit, let’s just concentrate on the function syntax.

::fn_trace_gettable is the actual name of the function we are using. The two parameters within the parenthesis basically specify the full path and file name and the number of trace files you want to import. By specifying default you are effectively saying that you want all of the trace files that have been created under that name. Multiple trace files of the same name will be suffixed with an underscore followed by a number, for example, a trace file called sql_trace.trc may have rolled over a number of times to create sql_trace_1.trc, sql_trace_2.trc etc. If you only want to import the first 5 trace files then just specify the number 5 instead of ‘default’. Simple.

So as you can see, you have a pretty handy little function that can import all of that trace data into a SQL table which you can then slice and dice until your heart is content.

Enjoy

PD

Hi there.

Have you ever had that annoying problem where you’ve created a cluster resource of type “share” (typically a shared folder on one of your SAN drives) that just keeps failing? If I had to pick a scenario I would chose log shipping from one cluster (Production) to another (Disaster recovery). So, you have your shared folder that contains your log backups but the resource keeps failing. Even worse, you’ve forgotten to un-check that box that affects the cluster group so that when the share fails it brings your entire group down? Yup, been there, done that. And if it’s a share that SQL needs access to then the chances are that you’ve put this share in your SQL cluster group and you’ve got a yo-yo’ing SQL Server service all because of a failing share. This would probably seem like an appropriate time to update your cv

Well this is your “get out of jail free” card.

First off, clear that check-box that reads “Affect the cluster group”. If your cluster share fails it’s really not that big a deal. Certainly not big enough to fail-over your cluster.

Secondly, I have a fix for you. You basically need to add in a key to your registry (ON BOTH NODES!! ) and here it is.

The registry path is HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\lanmanserver\parameters and you basically need to create a key called IRPStackSize and set the value to HEX 12.

That’s it. Simple.

Reboot the node (at a convenient time) and hey presto, no more failing service.

Remember to repeat on both nodes.

Enjoy

PD

You’ve no doubt seen the security bulletins issued by Microsoft over the last few months and if, like me, you’ve had to apply them to your SQL Server estate you may well have had some cause for confusion.

I’ve been testing, re-testing and testing just a little bit more, all of the KB’s that I have to deploy and I’ve started rolling them out onto our production boxes. But how do you know which KB you need to apply to your SQL instance? Well it is actually explained on the Microsoft site but I’ve got a very quick “at a glance” version, which is the reason for this post.

This is my crib-sheet for security bulletin MS08-040

MS08-052 Security bulletin crib sheet to follow soon….

Enjoy

PD

This is a handy little function that will decrypt the owner_id from the msdb sysjobs table. Just wanted to post it very quickly because I’ve just had to search for it yet again.

SELECT name, suser_sname(owner_sid) as owner
FROM
sysjobs

Enjoy

PD