SQL server SQLDIAG Utility

Posted By Sagar Patil

SQLDIAG.exe executable can be found in the SQL Server installation directory under the BINN folder. SQLDIAG records the SQL Server configuration, contents of the error logs (current, as well as archived logs), and Windows configuration including registry keys. If SQL Server is running when SQLDIAG is executed, it will record the output of the following routines:

  1. sp_configure
  2. sp_who
  3. sp_lock
  4. sp_helpdb
  5. xp_msver
  6. sp_helpextendedproc
  7. SELECT * FROM sysprocesses
  8. DBCC INPUTBUFFER for all active sessions
  9. SQLDIAG will also find the roots of any blocking issues
  10. Last 100 queries and exceptions

If SQL Server isn’t running, then SQLDIAG won’t collect SQL Server connection information and INPUTBUFFERs.SQLDIAG output will include the following information about Windows:

  1. Operating system report
  2. Hard drive report
  3. Memory report
  4. Processor report
  5. Services report
  6. Program groups report
  7. Startup programs report
  8. Environment variables report
  9. Software environment report
  10. Network connections report
  11. IRQ’s report
  12. Problem devices report

Generating the output of SQLDIAG can be helpful during disaster recovery since it contains the report of Windows / SQL Server configuration. Notethat you must run the SQLDIAG utility directly on the server; you cannot run it on a remote client.The full syntax of SQLDIAG is as follows:

>──sqldiag─┬───────────────────┬─┬───────────────────────────────────┬──>
           ├─ -? ──────────────┤ └─┬──────────────┬─┬──────────────┬─┘
           └─ -I instance_name─┘   └─ -U login_id─┘ ├─ -P password─┤
                                                    └─ -E ─────────┘

>─┬─────────────────┬─┬──────┬─┬──────┬─┬──────┬────────────────────────>
  └─ -O output_file─┘ └─ -X ─┘ └─ -M ─┘ └─ -C ─┘

Parameters are summarized in the following table:

 

Parameter Explanation
-? Returns SQLDIAG syntax
-I Instance name. If not specified SQLDIAG will attempt connecting to the default instance
-U SQL Server login or Windows login used for connecting
-P Password of the login specified with –U
-E This option advises SQLDIAG to use trusted connections, the password will not be provided. –E is mutually exclusive with –P
-O The name of the output file. Default name is SQLDIAG.txt. The file is stored in the SQL Server installation directory in LOG folder
-X Advises SQLDIAG to exclude error logs from the output. This is useful if the error logs are too large to be included. You can read error logs directly from ERRORLOG files.
-M Advises SQLDIAG to include DBCC STACKDUMP in its output
-C Advises SQLDIAG to include cluster information in the output

For example, the following command generates the SQLDIAG report on the default instance of SQL Server:

sqldiag

The output generated at the command line looks similar to the following:

Connecting to server SERVERNAME
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.1
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.2
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.3
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.4
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.5
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.6
Getting registry information
Getting library version information
Getting configuration information
Getting current user information
Getting lock information
Getting database information
Getting product information
Getting extended procedures information
Getting process information
Getting input buffers
Getting head blockers
Getting machine information. Please wait, this may take a few minutes
Data Stored in E:\Program Files\Microsoft SQL Server\MSSQL\log\SQLdiag.txt

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu