SQL Server 2005 Diagnostic Information Queries (Dec 2012)

Here is the December 2012 version of my SQL Server 2005 Diagnostic Information Queries, with some minor tweaks and improvements. I dropped a couple of redundant queries, and added a new query at the end.

To go with it is an updated, blank 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. With the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will want to enable the “Copy with Headers” option under Tools, Options,Query Results, SQL Server, Results to Grid in order to be able copy the results with headers.

These queries are designed to work on SQL Server 2005, although many of them will also work on newer versions of SQL Server. If you have a newer version of SQL Server you are really better off getting the proper version of these queries that matches your version of SQL Server.

All of the recent versions of my DMV diagnostic queries can be found here.

About these ads
This entry was posted in SQL Server 2005 and tagged , . Bookmark the permalink.

4 Responses to SQL Server 2005 Diagnostic Information Queries (Dec 2012)

  1. Michael says:

    Thanks so much Glenn, your queris help a lot in my daily work.

    Last new query may produce wrong results if someone has very long backup history, longer then 12 months. So I sugest to change this query a little:

    SELECT [database_name] AS [Database], SUBSTRING(CONVERT(VARCHAR, [backup_start_date], 20), 1, 7) AS [Month],
    CAST(AVG([backup_size]/1024/1024) AS DECIMAL(15,2)) AS [Backup Size (MB)]
    FROM msdb.dbo.backupset WITH (NOLOCK)
    WHERE [database_name] = DB_NAME(DB_ID())
    AND [type] = ‘D’
    — last 12 months only
    –and [backup_start_date] >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY [database_name],SUBSTRING(CONVERT(VARCHAR, [backup_start_date], 20), 1, 7) OPTION (RECOMPILE);

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

  3. Stephen says:

    Hi Glenn,
    I’ve just inherited 3 new-to-me SQL Servers running 2005.xxxx
    One is at 9.00.4053.00, which I notice isn’t in your list and shows in the results as “Build 3790: Service Pack 2″.
    It appears to be SP3 GDR. (http://support.microsoft.com/kb/970892)
    The other two are at 9.00.3073.00, also not in your list, so they also show up as “Build 3790: Service Pack 2″, which they aren’t. http://support.microsoft.com/kb/970892 appears to show they are at a security update build-level.
    Would it be possible to enhance your script for these builds, please?
    Obviously, and admitted by the current personnel, “these servers haven’t been touched in a while.”

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 )

Google+ photo

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

Connecting to %s