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.
Glenn Berry's SQL Server Performance 
Thanks very much Glenn, Wish you a very happy new year.
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
andreas.marner@web.de
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!