SQL Server 2008 Diagnostic Information Queries (December 2011)

Here is a fresh set of diagnostic information queries for SQL Server 2008 and 2008 R2 for December 2011. Some of the queries (which are labeled) will only work with SQL Server 2008 R2 Service Pack 1 or later. Most of the queries will also work with SQL Server 2012, but a few will not because Microsoft made some breaking changes to several DMVs between SQL Server 2008 and SQL Server 2012. If you have SQL Server 2012, you would be better off using the matching version of these queries meant for SQL Server 2012.

You will need VIEW SERVER STATE permission to run most of these queries. I recommend that you run each query separately, after reading the instructions and comments. One new feature is links to the Microsoft KB articles that list the builds for each branch of SQL Server 2008 and SQL Server 2008 R2.

I always like to hear your comments and suggestions about these queries.

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

3 Responses to SQL Server 2008 Diagnostic Information Queries (December 2011)

  1. Ponnusami says:

    Thanks very much Glenn, Wish you a very happy new year.

  2. Dear Mr. Berry
    Please do your very useful Diagnostic Information Queries in 4 complete separate versions for 2005, 2008, 2008R2 and 2012!
    A lot of DBAs in the world will be very happier and efficienter.
    I imported some of your queries in Powershell and the reports are in a better format.
    I wish you from Hannover, Germany a very good new year with health and successes
    Andreas Marner

  3. I have a question on one of your queries:

    — Volume info for all databases on the current instance (SQL Server 2008 R2 SP1 or greater)
    SELECT DB_NAME(f.database_id) AS [DatabaseName], f.file_id,
    vs.volume_mount_point, vs.total_bytes, vs.available_bytes,
    CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,1)) * 100 AS [Space Used %]
    FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
    ORDER BY f.database_id OPTION (RECOMPILE);

    You define Available/Total as Space_Used – wouldn’t this be Space_Free? Or am I misunderstanding one of the metrics involved?

    Thanks for all you do!

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 )

Google photo

You are commenting using your Google 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