SQLdiag Utility
The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft Customer Support Services.
Note
Beginning with SQL Server 2005, the SQLdiag utility has changed significantly. The command line arguments for this utility are not compatible with SQL Server 2000. This utility may be changed, and applications or scripts that rely on its command line arguments or behavior may not work correctly in future releases.
SQLdiag can collect the following types of diagnostic information:
Windows performance logs
Windows event logs
SQL Server Profiler traces
SQL Server blocking information
SQL Server configuration information
You can specify what types of information you want SQLdiag to collect by editing the configuration file SQLDiag.xml, which is described in a following section.
Syntax
sqldiag
{ [/?] }
|
{ [/I configuration_file]
[/O output_folder_path]
[/P support_folder_path]
[/N output_folder_management_option]
[/M machine1 [ machine2 machineN]| @machinelistfile]
[/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 }
Arguments
/?
Displays usage information./Iconfiguration_file
Sets the configuration file for SQLdiag to use. By default, /I is set to SQLDiag.Xml./Ooutput_folder_path
Redirects SQLdiag output to the specified folder. If the /O option is not specified, SQLdiag output is written to a subfolder named SQLDIAG under the SQLdiag startup folder. If the SQLDIAG folder does not exist, SQLdiag attempts to create it.Note
The output folder location is relative to the support folder location that can be specified with /P. To set an entirely different location for the output folder, specify the full directory path for /O.
/Psupport_folder_path
Sets the support folder path. By default, /P is set to the folder where the SQLdiag executable resides. The support folder contains SQLdiag support files, such as the XML configuration file, Transact-SQL scripts, and other files that the utility uses during diagnostics collection. If you use this option to specify an alternate support files path, SQLdiag will automatically copy the support files it requires to the specified folder if they do not already exist.Note
To set your current folder as the support path, specify %cd% on the command line as follows:
SQLDIAG /P %cd%
/Noutput_folder_management_option
Sets whether SQLdiag overwrites or renames the output folder when it starts up. Available options:1 = Overwrites the output folder (default)
2 = When SQLdiag starts up, it renames the output folder to SQLDIAG_00001, SQLDIAG_00002, and so on. After renaming the current output folder, SQLdiag writes output to the default output folder SQLDIAG.
Note
SQLdiag does not append output to the current output folder when it starts up. It can only overwrite the default output folder (option 1) or rename the folder (option 2), and then it writes output to the new default output folder named SQLDIAG.
/Mmachine1 [ machine2machineN] | @machinelistfile
Overrides the machines specified in the configuration file. By default the configuration file is SQLDiag.Xml, or is set with the /I parameter. When specifying more than one machine, separate each machine name with a space.Using @machinelistfile specifies a machine list filename to be stored in the configuration file.
/Cfile_compression_type
Sets the type of file compression used on the SQLdiag output folder files. Available options:0 = none (default)
1 = uses NTFS compression
/B [+]start_time
Specifies the date and time to start collecting diagnostic data in the following format:YYYYMMDD_HH:MM:SS
The time is specified using 24-hour notation. For example, 2:00 P.M. should be specified as 14:00:00.
Use + without the date (HH:MM:SS only) to specify a time that is relative to the current date and time. For example, if you specify /B +02:00:00, SQLdiag will wait 2 hours before it starts collecting information.
Do not insert a space between + and the specified start_time.
If you specify a start time that is in the past, SQLdiag forcibly changes the start date so the start date and time are in the future. For example, if you specify /B 01:00:00 and the current time is 08:00:00, SQLdiag forcibly changes the start date so that the start date is the next day.
Note that SQLdiag uses the local time on the computer where the utility is running.
/E [+]stop_time
Specifies the date and time to stop collecting diagnostic data in the following format:YYYYMMDD_HH:MM:SS
The time is specified using 24-hour notation. For example, 2:00 P.M. should be specified as 14:00:00.
Use + without the date (HH:MM:SS only) to specify a time that is relative to the current date and time. For example, if you specify a start time and end time by using /B +02:00:00 /E +03:00:00, SQLdiag waits 2 hours before it starts collecting information, then collects information for 3 hours before it stops and exits. If /B is not specified, SQLdiag starts collecting diagnostics immediately and ends at the date and time specified by /E.
Do not insert a space between + and the specified start_time or end_time.
Note that SQLdiag uses the local time on the computer where the utility is running.
/A SQLdiag_application_name
Enables running multiple instances of the SQLdiag utility against the same SQL Server instance.Each SQLdiag_application_name identifies a different instance of SQLdiag. No relationship exists between a SQLdiag_application_name instance and a SQL Server instance name.
SQLdiag_application_name can be used to start or stop a specific instance of the SQLdiag service.
For example:
SQLDIAG START /A SQLdiag_application_name
It can also be used with the /R option to register a specific instance of SQLdiag as a service. For example:
SQLDIAG /R /ASQLdiag_application_name
Note
SQLdiag automatically prefixes DIAG$ to the instance name specified for SQLdiag_application_name. This provides a sensible service name if you register SQLdiag as a service.
/T { tcp [ ,port ] | np | lpc | via }
Connects to an instance of SQL Server using the specified protocol.tcp [,port]
Transmission Control Protocol/Internet Protocol (TCP/IP). You can optionally specify a port number for the connection.np
Named pipes. By default, the default instance of SQL Server listens on named pipe \\.\pipe\sql\query and \\.\pipe\MSSQL$<instancename>\sql\query for a named instance. You cannot connect to an instance of SQL Server by using an alternate pipe name.lpc
Local procedure call. This shared memory protocol is available if the client is connecting to an instance of SQL Server on the same computer.via
Virtual Interface Adapter protocol. Use for VIA hardware. For information about how to use VIA, contact your hardware vendor.Note
The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
For more information about protocols, see Choosing a Network Protocol.
/Q
Runs SQLdiag in quiet mode. /Q suppresses all prompts, such as password prompts./G
Runs SQLdiag in generic mode. When /G is specified, on startup SQLdiag does not enforce SQL Server connectivity checks or verify that the user is a member of the sysadmin fixed server role. Instead, SQLdiag defers to Windows to determine whether a user has the appropriate rights to gather each requested diagnostic.If /G is not specified, SQLdiag checks to determine whether the user is a member of the Windows Administrators group, and will not collect SQL Server diagnostics if the user is not an Administrators group member.
/R
Registers SQLdiag as a service. Any command line arguments that are specified when you register SQLdiag as a service are preserved for future runs of the service.When SQLdiag is registered as a service, the default service name is SQLDIAG. You can change the service name by using the /A argument.
Use the START command line argument to start the service:
SQLDIAG START
You can also use the net start command to start the service:
net start SQLDIAG
/U
Unregisters SQLdiag as a service.Use the /A argument also if unregistering a named SQLdiag instance.
/L
Runs SQLdiag in continuous mode when a start time or end time is also specified with the /B or /E arguments, respectively. SQLdiag automatically restarts after diagnostics collection stops due to a scheduled shutdown. For example, by using the /E or the /X arguments.Note
SQLdiag ignores the /L argument if a start time or end time is not specified by using the /B and /E command line arguments.
Using /L does not imply the service mode. To use /L when running SQLdiag as a service, specify it on the command line when you register the service.
/X
Runs SQLdiag in snapshot mode. SQLdiag takes a snapshot of all configured diagnostics and then shuts down automatically.START | STOP | STOP_ABORT
Starts or stops the SQLdiag service. STOP_ABORT forces the service to shut down as quickly as possible without finishing collection of diagnostics it is currently collecting.When these service control arguments are used, they must be the first argument used on the command line. For example:
SQLDIAG START
Only the /A argument, which specifies a named instance of SQLdiag, can be used with START, STOP, or STOP_ABORT to control a specific instance of the SQLdiag service. For example:
SQLDIAG START /ASQLdiag_application_name
Security Requirements
Unless SQLdiag is run in generic mode (by specifying the /G command line argument), the user who runs SQLdiag must be a member of the Windows Administrators group and a member of the SQL Server sysadmin fixed server role. By default, SQLdiag connects to SQL Server by using Windows Authentication, but it also supports SQL Server Authentication.
Performance Considerations
The performance effects of running SQLdiag depend on the type of diagnostic data you have configured it to collect. For example, if you have configured SQLdiag to collect SQL Server Profiler tracing information, the more event classes you choose to trace, the more your server performance is affected.
The performance impact of running SQLdiag is approximately equivalent to the sum of the costs of collecting the configured diagnostics separately. For example, collecting a trace with SQLdiag incurs the same performance cost as collecting it with SQL Server Profiler. The performance impact of using SQLdiag is negligible.
Required Disk Space
Because SQLdiag can collect different types of diagnostic information, the free disk space that is required to run SQLdiag varies. The amount of diagnostic information collected depends on the nature and volume of the workload that the server is processing and may range from a few megabytes to several gigabytes.
Configuration Files
On startup, SQLdiag reads the configuration file and the command line arguments that have been specified. You specify the types of diagnostic information that SQLdiag collects in the configuration file. By default, SQLdiag uses the SQLDiag.Xml configuration file, which is extracted each time the tool runs and is located in the SQLdiag utility startup folder. The configuration file uses the XML schema, SQLDiag_schema.xsd, which is also extracted into the utility startup directory from the executable file each time SQLdiag runs.
Editing the Configuration Files
You can copy and edit SQLDiag.Xml to change the types of diagnostic data that SQLdiag collects. When editing the configuration file always use an XML editor that can validate the configuration file against its XML schema, such as Management Studio. You should not edit SQLDiag.Xml directly. Instead, make a copy of SQLDiag.Xml and rename it to a new file name in the same folder. Then edit the new file, and use the /I argument to pass it to SQLdiag.
Editing the Configuration File When SQLdiag Runs as a Service
If you have already run SQLdiag as a service and need to edit the configuration file, unregister the SQLDIAG service by specifying the /U command line argument and then re-register the service by using the /R command line argument. Unregistering and re-registering the service removes old configuration information that was cached in the Windows registry.
Output Folder
If you do not specify an output folder with the /O argument, SQLdiag creates a subfolder named SQLDIAG under the SQLdiag startup folder. For diagnostic information collection that involves high volume tracing, such as SQL Server Profiler , make sure that the output folder is on a local drive with enough space to store the requested diagnostic output.
When SQLdiag is restarted, it overwrites the contents of the output folder. To avoid this, specify /N 2 on the command line.
Data Collection Process
When SQLdiag starts, it performs the initialization checks necessary to collect the diagnostic data that have been specified in SQLDiag.Xml. This process may take several seconds. After SQLdiag has started collecting diagnostic data when it is run as a console application, a message displays informing you that SQLdiag collection has started and that you can press CTRL+C to stop it. When SQLdiag is run as a service, a similar message is written to the Windows event log.
If you are using SQLdiag to diagnose a problem that you can reproduce, wait until you receive this message before you reproduce the problem on your server.
SQLdiag collects most diagnostic data in parallel. All diagnostic information is collected by connecting to tools, such as the SQL Server sqlcmd utility or the Windows command processor, except when information is collected from Windows performance logs and event logs. SQLdiag uses one worker thread per computer to monitor the diagnostic data collection of these other tools, often simultaneously waiting for several tools to complete. During the collection process, SQLdiag routes the output from each diagnostic to the output folder.
Stopping Data Collection
After SQLdiag starts collecting diagnostic data, it continues to do so unless you stop it or it is configured to stop at a specified time. You can configure SQLdiag to stop at a specified time by using the /E argument, which allows you to specify a stop time, or by using the /X argument, which causes SQLdiag to run in snapshot mode.
When SQLdiag stops, it stops all diagnostics it has started. For example, it stops SQL Server Profiler traces it was collecting, it stops executing Transact-SQL scripts it was running, and it stops any sub processes it has spawned during data collection. After diagnostic data collection has completed, SQLdiag exits.
Note
Pausing the SQLdiag service is not supported. If you attempt to pause the SQLdiag service, it stops after it finishes collecting the diagnostics that it was collecting when you paused it. If you restart SQLdiag after stopping it, the application restarts and overwrites the output folder. To avoid overwriting the output folder, specify /N 2 on the command line.
To stop SQLdiag when running as a console application
If you are running SQLdiag as a console application, press CTRL+C in the console window where SQLdiag is running to stop it. After you press CTRL+C, a message displays in the console window informing you that SQLDiag data collection is ending, and that you should wait until the process shuts down, which may take several minutes.
Press Ctrl+C twice to terminate all child diagnostic processes and immediately terminate the application.
To stop SQLdiag when running as a service
If you are running SQLdiag as a service, run SQLDiag STOP in the SQLdiag startup folder to stop it.
If you are running multiple instances of SQLdiag on the same computer, you can also pass the SQLdiag instance name to on the command line when you stop the service. For example, to stop a SQLdiag instance named Instance1, use the following syntax:
SQLDIAG STOP /A Instance1
Note
/A is the only command-line argument that can be used with START, STOP, or STOP_ABORT. If you need to specify a named instance of SQLdiag with one of the service control verbs, specify /A after the control verb on the command line as shown in the previous syntax example. When control verbs are used, they must be the first argument on the command line.
To stop the service as quickly as possible, run SQLDIAG STOP_ABORT in the utility startup folder. This command aborts any diagnostics collecting currently being performed without waiting for them to finish.
Note
Use SQLDiag STOP or SQLDIAG STOP_ABORT to stop the SQLdiag service. Do not use the Windows Services Console to stop SQLdiag or other SQL Server services.
Automatically Starting and Stopping SQLdiag
To automatically start and stop diagnostic data collection at a specified time, use the /Bstart_time and /Estop_time arguments, using 24-hour notation. For example, if you are troubleshooting a problem that consistently appears at approximately 02:00:00, you can configure SQLdiag to automatically start collecting diagnostic data at 01:00 and automatically stop at 03:00:00. Use the /B and /E arguments to specify the start and stop time. Use 24-hour notation to specify an exact start and stop date and time with the format YYYYMMDD_HH:MM:SS. To specify a relative start or stop time, prefix the start and stop time with + and omit the date portion (YYYYMMDD_) as shown in the following example, which causes SQLdiag to wait 1 hour before it starts collecting information, then it collects information for 3 hours before it stops and exits:
sqldiag /B +01:00:00 /E +03:00:00
When a relative start_time is specified, SQLdiag starts at a time that is relative to the current date and time. When a relative end_time is specified, SQLdiag ends at a time that is relative to the specified start_time. If the start or end date and time that you have specified is in the past, SQLdiag forcibly changes the start date so that the start date and time are in the future.
This has important implications on the start and end dates you choose. Consider the following example:
sqldiag /B +01:00:00 /E 08:30:00
If the current time is 08:00, the end time passes before diagnostic collection actually begins. Because SQLDiag automatically adjusts start and end dates to the next day when they occur in the past, in this example diagnostic collection starts at 09:00 today (a relative start time has been specified with +) and continues collecting until 08:30 the following morning.
Stopping and Restarting SQLdiag to Collect Daily Diagnostics
To collect a specified set of diagnostics on a daily basis without having to manually start and stop SQLdiag, use the /L argument. The /L argument causes SQLdiag to run continuously by automatically restarting itself after a scheduled shutdown. When /L is specified, and SQLdiag stops because it has reached the end time specified with the /E argument, or it stops because it is being run in snapshot mode by using the /X argument, SQLdiag restarts instead of exiting.
The following example specifies that SQLdiag run in continuous mode to automatically restart after diagnostic data collecting occurs between 03:00:00 and 05:00:00.
sqldiag /B 03:00:00 /E 05:00:00 /L
The following example specifies that SQLdiag run in continuous mode to automatically restart after taking a diagnostic data snapshot at 03:00:00.
sqldiag /B 03:00:00 /X /L
Running SQLdiag as a Service
When you want to use SQLdiag to collect diagnostic data for long periods of time during which you might need to log out of the computer on which SQLdiag is running, you can run it as a service.
To register SQLDiag to run as a service
You can register SQLdiag to run as a service by specifying the /R argument at the command line. This registers SQLdiag to run as a service. The SQLdiag service name is SQLDIAG. Any other arguments you specify on the command line when you register SQLDiag as a service are preserved and reused when the service is started.
To change the default SQLDIAG service name, use the /A command-line argument to specify another name. SQLdiag automatically prefixes DIAG$ to any SQLdiag instance name specified with /A to create sensible service names.
To unregister the SQLDIAG service
To unregister the service, specify the /U argument. Unregistering SQLdiag as a service also deletes the Windows registry keys of the service.
To start or restart the SQLDIAG service
To start or restart the SQLDIAG service, run SQLDiag START from the command line.
If you are running multiple instances of SQLdiag by using the /A argument, you can also pass the SQLdiag instance name on the command line when you start the service. For example, to start a SQLdiag instance named Instance1, use the following syntax:
SQLDIAG START /A Instance1
You can also use the net start command to start the SQLDIAG service.
When you restart SQLdiag, it overwrites the contents in the current output folder. To avoid this, specify /N 2 on the command line to rename the output folder when the utility starts.
Pausing the SQLdiag service is not supported.
Running Multiple Instances of SQLdiag
Run multiple instances of SQLdiag on the same computer by specifying /ASQLdiag_application_name on the command line. This is useful for collecting different sets of diagnostics simultaneously from the same SQL Server instance. For example, you can configure a named instance of SQLdiag to continuously perform lightweight data collection. Then, if a specific problem occurs on SQL Server, you can run the default SQLdiag instance to collect diagnostics for that problem, or to gather a set of diagnostics that Microsoft Customer Support Services has asked you to gather to diagnose a problem.
Collecting Diagnostic Data from Clustered SQL Server Instances
SQLdiag supports collecting diagnostic data from clustered SQL Server instances. To gather diagnostics from clustered SQL Server instances, make sure that "." is specified for the name attribute of the <Machine> element in the configuration file SQLDiag.Xml and do not specify the /G argument on the command line. By default, "." is specified for the name attribute in the configuration file and the /G argument is turned off. Typically, you do not need to edit the configuration file or change the command line arguments when collecting from a clustered SQL Server instance.
When "." is specified as the machine name, SQLdiag detects that it is running on a cluster, and simultaneously retrieves diagnostic information from all virtual instances of SQL Server that are installed on the cluster. If you want to collect diagnostic information from only one virtual instance of SQL Server that is running on a computer, specify that virtual SQL Server for the name attribute of the <Machine> element in SQLDiag.Xml.
Note
To collect SQL Server Profiler trace information from clustered SQL Server instances, administrative shares (ADMIN$) must be enabled on the cluster.