SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

February 2011 Version of SQL Server 2005 and 2008 Diagnostic Information Queries

Wow, it is already February 2011, so here are the February 2011 versions of the SQL Server 2005 Diagnostic Information Queries and the SQL Server 2008 Diagnostic Information Queries.

To go with them are the matching blank results spreadsheets for SQL Server 2005 and SQL Server 2008. The spreadsheets have their tabs labeled in order to match the queries. That way, it is easy to run each query, click the top left box in the results grid to select all columns and rows, right-click and select “Copy with Headers” to copy all of the results to the Windows clipboard. Then you can paste the results to the appropriate tab in the spreadsheet.

I also want to remind people that Mainstream Support for SQL Server 2005 ends on April 12, 2011 (even if you are on SP4).  This is a good excuse to push for an upgrade, especially if you are running SQL Server 2005 on top of Windows Server 2003 or 2003 R2, which are both out of Mainstream Support.

If you are still using the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will need to make sure you change a setting under “Tools”, “Options”, “Query Results”, “SQL Server”, “Results to Grid”, to enable “Include column headers when copying or saving the results”, like you see below, before you run the queries.


Otherwise, you will lose the column headers, which makes the results much harder to interpret.

As always, you will need to have VIEW SERVER STATE permission to run most of the DMV queries.

I advise you to run each query one at a time, after reading the comments. The last query in the script (to check index fragmentation) could take quite a while to run, depending on your database size and your hardware infrastructure.


Posted by cjones on 3 February 2011

When I run the last query in the file to get fragmentation information, I get an error because I have some offline databses.

Msg 942, Level 14, State 4, Line 1

Database 'GW_Common' cannot be opened because it is offline.

This can be prevented by changing line

FROM sys.dm_db_index_physical_stats( NULL,NULL, NULL, NULL ,'LIMITED') AS ps


FROM sys.dm_db_index_physical_stats( DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps

Posted by Glenn Berry on 3 February 2011

Good point. I have modified the last query to make that change. Thanks!

Posted by Ahad Shaikh on 10 February 2011

Thanks, Its really helpful to me.. Thanks again

Posted by Glenn Berry on 10 February 2011

Glad you like them.

Leave a Comment

Please register or log in to leave a comment.