SQL Server 2008 Command-Line Utilities : The sqldiag Command-Line Utility

10/10/2010 6:16:06 PM

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:

{ [/?] }
{ [/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] /A SQLdiag_application_name }


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.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone