SQL Server 2008 Diagnostic Information Queries (Dec 2012)

Here is the December 2012 version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a couple of the existing queries. There is also one new query at the very end, adapted from Erin Stellato.

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.

You should be aware that both Query 46 and Query 49 could take some time to run, depending on the size of your database and the speed of your hardware and storage subsystem.

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

3 Responses to SQL Server 2008 Diagnostic Information Queries (Dec 2012)

  1. Chris Yates says:

    Christmas came early for me! Thanx man!

  2. Hey Glenn,

    Thanks for this new version!
    I’ve modified the query “Individual File Sizes and space available for current database (Query 32)” so that the results are available for all the databases on the server instead of one single DB.
    I’m using it a lot when auditing my customers servers.

    USE tempdb
    create table #DatabaseUsage (
    [Database Name] sysname,
    [File Name] sysname,
    [File Type] varchar(10),
    [Filegroup Name] sysname null,
    [Physical Name] varchar(256),
    [Total Size In MB] decimal(15,2),
    [Space Used In MB] decimal(15,2),
    [Available Space In MB] decimal(15,2)

    SET @CMD = N’use [?]
    SELECT db_name()as [Database Name],f.name AS [File Name],
    CASE f.type when 0 then ”DATA” when 1 then ”LOG” when 2 then ”FILESTREAM” when 4 then ”FULL-TEXT”
    else ”OTHER” END as Filetype,fg.name AS [Filegroup Name],
    physical_name AS [Physical Name], CAST(size/128.0 as decimal(15,2)) AS [Total Size in MB],
    CAST(FILEPROPERTY(f.name, ”SpaceUsed”) /128.0 AS decimal(15,2)) as [Space Used in MB],
    CAST(size/128.0 as decimal(15,2)) – CAST(FILEPROPERTY(f.name, ”SpaceUsed”) /128.0 AS decimal(15,2)) AS [Available Space In MB]
    FROM sys.database_files f WITH (NOLOCK)
    LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
    ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);’

    Insert into #DatabaseUsage
    exec sp_MSForEachDB @CMD

    select [Database Name],[File Name],[File Type],[Filegroup Name],[Physical Name],[Total Size In MB],[Space Used In MB],[Available Space In MB]
    from #DatabaseUsage
    drop table #DatabaseUsage

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

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