sqldiag
is a diagnostic tool that you can use to gather diagnostic information
about various SQL Server services. It is intended for use by Microsoft
support engineers, but you might also find the information it gathers
useful in troubleshooting a problem. sqldiag collects the information into files that are written, by default, to a folder named SQLDIAG, which is created where the file sqldiag.exe is located (for example, C:\Program Files\Microsoft SQL Server\100\Tools\binn\SQLDIAG\).
The folder holds files that contain information about the machine on
which SQL Server is running in addition to the following types of
diagnostic information:
SQL Server configuration information
SQL Server blocking output
SQL Server Profiler traces
Windows performance logs
Windows event logs
The syntax for sqldiag
changed quite a bit in SQL Server 2005, but very little has changed in
SQL Server 2008. Some of the options that were used in versions prior
to SQL Server 2005 are not compatible with the current version. The
full syntax for sqldiag is as follows:
sqldiag
{ [/?] }
|
{ [/I configuration_file]
[/O output_folder_path]
[/P support_folder_path]
[/N output_folder_management_option]
[/C file_compression_type]
[/B [+]start_time]
[/E [+]stop_time]
[/A SQLdiag_application_name]
[/T { tcp [ ,port ] | np | lpc | via } ]
[/Q] [/G] [/R] [/U] [/L] [/X] }
|
{ [START | STOP | STOP_ABORT] }
|
{ [START | STOP | STOP_ABORT] /A SQLdiag_application_name }
Note
Keep in mind that many of the options for sqldiag identify how and when the sqldiag
utility will be run. The utility can be run as a service, scheduled to
start and stop at a specific time of day, and it can be configured to
change the way the output is generated. The details about these options
are beyond the scope of this chapter but are covered in detail in SQL
Server Books Online. This section is intended to give you a taste of
the useful information that this utility can capture.
By default, the sqldiag
utility must be run by a member of the Windows Administrators group,
and this user must also be a member of the sysadmin fixed SQL Server
role. To get a flavor for the type of information that sqldiag outputs, open a command prompt window, change the directory to the location of the sqldiag.exe file, and type the following command:
No parameters are needed
to generate the output. The command prompt window scrolls status
information across the screen as it collects the diagnostic
information. You see the message “SQLDIAG Initialization starting...”
followed by messages that indicate what information is being collected.
The data collection includes a myriad of system information from MSINFO32, default traces, and SQLDumper log files. When you are ready to stop the collection, you can press Ctrl+C.
If you navigate to the sqldiag output folder, you find the files created during the collection process. In this output folder, you should find a file with MSINFO32
in its name. This file contains the same type of information that you
see when you launch the System Information application from Accessories
or when you run MSINFO32.EXE. This is
key information about the machine on which SQL Server is running. This
information includes the number of processors, the amount of memory,
the amount of disk space, and a slew of other hardware and software
data.
You also find a file named xxx_sp_sqldiag_Shutdown.out, where xxx
is the name of the SQL Server machine. This file contains SQL
Server–specific information, including the SQL Server error logs,
output from several key system stored procedures, including sp_helpdb and sp_configure, and much more information related to the current state of SQL Server.
You find other files in the sqldiag output directory as well. Default trace files, log files related to the latest sqldiag
execution, and a copy of the XML file containing configuration
information are among them. Microsoft documentation on these files is
limited, and you may find that the best way to determine what they
contain is simply to open the files and review the wealth of
information therein.
