SQLDiag is a diagnostic tool that you can use to gather information regarding various SQL Server services. It is intended for use by Microsoft support engineers, but you might also find the information that it gathers useful in troubleshooting a problem. SQLDiag collects the information into a text file named Sqldiag.txt that is located in the log directory for SQL Server, typically Program Files\Microsoft SQL Server\MSSQL$ instance_name\log\. The file contains all the SQL Server error logs, Registry data, file versions, configuration data, user and process information, and output from the Microsoft diagnostic utility (Winmsd.exe).

The syntax for SQLDiag is as follows:

[-?] |
[-I instance_name]
[ [-U login_ID] [-P password] | [-E] ]
[-O output_file]
[-X] [-M] [-C]

The ?I parameter can be used to specify the SQL Server 2000 instance name. In situations in which you have multiple versions of SQL Server running, you will want to use the instance parameter. Otherwise, it might gather information on the 7.0 server instead of the 2000 server. The ?O option can be used to specify an alternative location for the output text file, and the ?X option can be used to streamline the output file by excluding the error logs from the file.

A portion of a sample sqldiag.txt file follows:

Drives Report 
C:\  (Local - NTFS)  Total: 4,120,168 KB, Free: 1,332,071 KB
D:\  (Local - NTFS)  Total: 13,592,876 KB, Free: 3,815,028 KB
E:\  (CDROM - CDFS) X06-07018 Total: 3,959,392 KB, Free: 0 KB

Memory Report
Handles: 6,273
Threads: 371
Processes: 58

Physical Memory (K)
   Total: 261,492
   Available: 64,420
   File Cache: 46,744

Again, this is just part of the output found in the text file, but you can see how useful this type of information can be.


I am a big fan of using SQLDiag to quickly retrieve a myriad of information about the remote database servers that I support. You can execute the SQLDiag.exe program utilizing the xp_cmdshell utility from Query Analyzer and transfer the resulting file back to your office for analysis.

Realize that the file can be fairly busy with all of the error logs contained in it. However, you can easily jump past the error log data by doing a find on 'registry information' in the text file or running the SQLDiag utility with the ?X parameter that excludes the error logs. I find the system drives and memory reports to be the most valuable sections in the text.

The SQLDIAG.EXE file is located, by default, in a path that is specific to the SQL Server instance. Therefore, one EXE is installed for each instance that is created. The path will typically be Program Files\Microsoft SQL Server\MSSQL$instance_name\Binn\.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features