SQL Server 2008 Diagnostic Information Queries (July 2012)

Here is the latest version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a number of the existing queries, and one new query. To go with it is an updated, blank 2008 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select โ€œCopy with Headersโ€ to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

These queries will work on both SQL Server 2008 and SQL Server 2008 R2 (although some of them will only work on SQL Server 2008 R2 SP1 or later). This is indicated in the instructions for each query.

The July 2012 versions for SQL Server 2005 and SQL Server 2012 will be available in the next day or so. As always, let me know what you think of these queries.

This entry was posted in SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

14 Responses to SQL Server 2008 Diagnostic Information Queries (July 2012)

  1. Thank you for the update. Both the links points to the same SQL script file. Could you please update the spreadsheet link.

  2. Stanislav says:


    Do you know something like your set of scripts but for Oracle?
    Thanks in advance!

  3. fishnchippapers says:

    Glenn. We are running database mirroring. As a result, DBMIRROR_EVENTS_QUEUE and DBMIRRORING_CMD dominate the results of query 20 (they account for more than 92% of the total waits). We also see DBMIRROR_DBM_EVENT. As far as I can determine, the first two are a consequence of mirroring just waiting for something to do, whilst the last is “Identified for informational purposes only. Not supported. Future compatibility is not guaranteed” in SQL Server 2008. If my analysis is correct would it be worth adding these to the list of exclusions?

  4. alzdba says:

    I noticed the node name behaves buggy when including the is_clustered column in the select list.
    ( Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) )

    removing the is_clustered column from your query shows the correct data in column cluster_nodename.

    Select * from sys.dm_server_services shows the correct data.

    Thank you for these great sets of inventory queries.

  5. Francisco Ramos says:

    Hi Glenn. Just discovered your blog a couple of weeks ago and I found your diag queries very useful, so I’ve spent 3 hours of my job time working on a simple windows forms application that parses and runs your diagnostic queries on a remote server without the use of SMSS and all the copy&paste ๐Ÿ˜€
    It’s only a 27kb exe. There must be a “query file” named queries.sql on the same location, that should be your query file. i’ve used the one in this post. The only modification I’ve made is commenting lines 539 and 540 (Use Yourdatabase) as they are queries that cannot be directly launched.

    Your file is so coherent in its format that’s been really easy to do this ๐Ÿ™‚
    It’s not fully debugged, but it’s a good start and it works

    Feel free to test it, make suggestions, etc.
    Release files: http://personales.gestion.unican.es/ramosf/sqldiagqueries/sqldiagqueries_release.zip
    Source code: http://personales.gestion.unican.es/ramosf/sqldiagqueries/sqldiagqueries_source.zip

    Maybe it’s a good first step for something in Codeplex!

  6. Pingback: SQL Server – Diagnostic Information Queries (2005/2008/2012) « Alex Souza

  7. Roni says:

    Thanks very much.
    Amazing set of queries, very useful !
    Have a great day ๐Ÿ™‚

  8. John Langston says:

    Query #17 (VLF Counts) honks with ‘column name or number of supplied values does not match table definition’. I don’t have a RecoveryUnitID in the results of DBCC LOGINFO, so I omit that column from the temp table definition and all is good. Thanks for some very useful scripts, Glenn.

  9. Sonny says:

    Link is not working…Giving 404 not found message…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s