Space / usage per table

This very short script will loop through all of your (user created) tables and return the number of rows and space used. Very useful indeed.

SET NOCOUNT OFF

DECLARE @tblName VARCHAR(255)
DECLARE tblCursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = ‘U’ ORDER BY name

OPEN tblCursor

FETCH NEXT FROM tblCursor
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
  
   EXEC sp_spaceused @tblName

   FETCH NEXT FROM tblCursor
   INTO @tblName
END

CLOSE tblCursor
DEALLOCATE tblCursor

Managing and analysing multiple trace files

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:

SELECT * INTO [db_name]..[table_name]

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 analyse the data until your heart is content.

 

My Windows “Share” cluster resource keeps failing

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.

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!