SQL Server 2005 Diagnostic Information Queries (August 2012)

Here is the August 2012 version of my SQL Server 2005 Diagnostic Information Queries, with some minor tweaks and improvements. 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.

6 Responses to SQL Server 2005 Diagnostic Information Queries (August 2012)

  1. Pingback: Something for the Weekend - SQL Server Links 24/08/12

  2. Gary says:

    I get this error:
    Msg 213, Level 16, State 7, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    on this:

    – Get VLF Counts for all databases on the instance (Query 19)
    – (adapted from Michelle Ufford)
    CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID int,
    FileSize bigint, StartOffset bigint,
    FSeqNo bigint, [Status] bigint,
    Parity bigint, CreateLSN numeric(38));

    CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);

    EXEC sp_MSforeachdb N’Use [?];

    INSERT INTO #VLFInfo
    EXEC sp_executesql N”DBCC LOGINFO([?])”;

    INSERT INTO #VLFCountResults
    SELECT DB_NAME(), COUNT(*)
    FROM #VLFInfo;

    TRUNCATE TABLE #VLFInfo;’

    SELECT DatabaseName, VLFCount
    FROM #VLFCountResults
    ORDER BY VLFCount DESC;

    DROP TABLE #VLFInfo;
    DROP TABLE #VLFCountResults;

  3. Naz says:

    Hi Glenn, is there any way to have this query run automatically say every month, and collate the results into a database? Thanks

  4. FishNChipPapers says:

    Thanks for the wonderfully useful scripts. I have recently taken on the role of DBA and have been asked to review the current SQL Server deployment, administration, configuration and so forth. I am using your scripts to assist in this process together with a number of other tools and techniques.

    I am struggling, however, to define a structure for the report of my findings and was hoping you might have some recommendations on an appropriate outline based on what has proved successful in communicating to technical and non-technical audiences.

  5. chammond1 says:

    Hello I am new to this posting Glenn Berry’s SQL Server performance and I have some questions. I have a SQL 2008 R2 server and wanted to run the report that goes with it in the Excel Sheet. Can someone give me some insight on where to start with this? It would be greatly appreciated.

    Thank you

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