PSSDIAG data collection utility

View products that this article applies to.

Summary

PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collection can be customized by enabling or disabling any of these log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture. PSSDIAG can also run custom utilities or custom Transact-SQL scripts for support cases that require data outside the natively supported diagnostic types.

↑ Back to the top

More Information

This documentation applies to the version of PSSDIAG that Microsoft Product Support Services sends to assist with troubleshooting of support cases. A slightly different version of the tool is available for public download. For documentation that focuses on the public downloadable version of the tool, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/aa175399(SQL.80).aspx

For Microsoft SQL Server 2005 and later versions, use the Pssdiag and Sqldiag manager that can be found on the following codeplex site:

http://diagmanager.codeplex.com/

For more information, visit the following MSDN website:

http://blogs.msdn.com/b/psssql/archive/2011/05/24/pssdiag-sqldiag-configuration-manager-released-to-codeplex.aspx

For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to obtain Microsoft support files from online services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

How to start PSSDIAG

PSSDIAG expands into the C:\PSSDIAG folder that it creates on your computer. This folder will need to be on a drive that has sufficient disk space available to hold the data and the log files that PSSDIAG has been configured to capture. Because PSSDIAG can be configured to collect many different log types, the free disk space that is required may vary from a megabyte or two, up to several gigabytes. Be aware that sometimes the amount of trace data collected depends on the nature and the volume of the workload that the server is processing. Therefore, a precise estimate may not be possible. For data collection that involves high-volume trace types such as SQL Profiler tracing, make sure that PSSDIAG is run from a local drive, not from a network share or a from a mapped network drive.

Generally, you will run PSSDIAG locally on the server that is being monitored. However, you can configure PSSDIAG to monitor a remote server. For more information about how to run PSSDIAG remotely, see the "Running PSSDIAG remotely or on a clustered SQL Server" section of this article. Note that running PSSDIAG remotely means that the data collected and traced will be sent over the network to a remote computer. This could introduce moderate to significant impact on your SQL Server performance depending on the network bandwidth and speed as well as I/O and processing capability of the remote system where data is written.

Note You may have to perform an extra step if you are collecting data from a clustered instance of SQL Server. Make sure to read the "How to Run PSSDIAG Remotely or on a Clustered SQL Server" section of this article, if you are connecting to a clustered instance of SQL Server, even if PSSDIAG will be run locally on the server.

Pssdiag.exe does not have significant inherent security requirements. However, Microsoft Windows NT administrator credentials are required for many of the diagnostics that PSSDIAG can be optionally configured to capture. Also, you must have sysadmin credentials on SQL Server if PSSDIAG is to capture diagnostics from SQL Server (that is, if PSSDIAG is not running in a "generic" mode with the /G command-line switch). By default, PSSDIAG will make a Windows authenticated connection to SQL Server; however, you can use SQL Server authentication if you want.

↑ Back to the top

PSSDIAG supports several optional command-line parameters. For more information about the optional command-line parameters, see the "PSSDIAG Command Line Parameters" section. However, generally it is not necessary to use the optional parameters. After you have extracted the PSSDIAG files from the package on the Microsoft Download Center, run Pssdiag.exe to start the data collection.

When PSSDIAG starts, it first opens, and then configures the log files it has been configured to capture. This process may take several seconds. When PSSDIAG is fully started and all the logs are active, it will output the following message to the console:

2003/10/02 12:30:14.90 PSSDIAG Collection started. Press Ctrl+C to stop.

If you are running PSSDIAG to collect data about a problem that you can reproduce at will, wait until you receive the message before you try to reproduce the problem.

Do not log out of the console session where PSSDIAG is running before the data collection is complete, and PSSDIAG has been shut down. Because PSSDIAG is a console utility, not a service, logging out of the session where PSSDIAG is running will shut down the utility and end data collection. You can run PSSDIAG from a Terminal Server session if you want, and you can disconnect the session instead of logging out to leave PSSDIAG running.

How to stop PSSDIAG

To stop PSSDIAG, press CTRL+C in the console window where PSSDIAG is running. Note that it is also possible to instruct PSSDIAG to shut itself down automatically at a particular time. For more information about this, see the "Automatically Starting and Stopping PSSDIAG" section. If PSSDIAG is automatically adding files to a compressed .cab file, it may take quite a while for PSSDIAG to finish compressing the final log files. After you press CTRL+C, PSSDIAG will send a message, that is similar to the following, to the console:

2003/10/02 12:24:00.69 PSSDIAG Ending data collection. wait while the process shuts down and files are compressed (this may take several minutes)

After this message appears, PSSDIAG is no longer collecting additional data from your server, even though it may continue to compress previously collected data.

Before PSSDIAG shuts down completely, it may prompt you with a message similar to:

The files in F:\pssdiag\output\ have been added to PSSDIAG.CAB. Delete the backups in F:\pssdiag\output\backup\?

When possible, Microsoft recommends that you answer with "N" to retain the collected data files in the Backup folderuntil you can confirmthat the Pssdiag.cab output file is intact.

Location of PSSDIAG output

Unless you specify a custom output folder by using the /O command-line parameter, PSSDIAG creates a folder that is named Output in the folder where it is run. If you are running PSSDIAG with the /C0 (default) or the /C1 command-line parameters to disable automatic compression, the output files will remain in this folder. You may want to compress the Output folder with the tool of your choice if you have to upload the folder to a Microsoft support professional.

If PSSDIAG is operating in automatic compression mode, it will add all the output files to a compressed Pssdiag.cab file in the Output folder. By default, PSSDIAG will not perform any compression. After a file has been successfully added to the Pssdiag.cab file, it will be moved to a separate folder that is named Backup. The Backup folder is created in the Output folder. Note that the maximum amount of uncompressed data that can be added to a single CAB file is 2-gigabytes (GB). If the data collected exceeds 2 GB, additional CAB files named Pssdiag2.cab, Pssdiag3.cab, and so on, will be created.

Removing PSSDIAG

PSSDIAG does not register any COM objects, copy any files to system directories, or modify the system registry. To remove PSSDIAG when data collection is complete, delete the folder that contains the PSSDIAG files. PSSDIAG does install several system stored procedures in the master database. These stored procedures are automatically removed when PSSDIAG shuts down.

PSSDIAG command line parameters

You can run PSSDIAG /? from the command-line to see a list of the command-line parameters that PSSDIAG supports. The most frequently used parameters are described in the following table. All these command-line parameters are optional.

Parameter	Description
/Q	Quiet mode. Suppresses prompts that require user interaction, such as the prompt to delete the backup files.
/C#	/C0 disables automatic compression, and /C1 enables NTFS compression for files in the OUTPUT directory. /C0 (no compression) is the default.
/B YYYYMMDD_HH:MM:SS	Specifies a future start time to start collection. PSSDIAG will remain idle until this time is reached. The date and time must be provided in the exact form that is specified here. The date and the time can be specified together or separately. For example, you can specify the time only or the date only.
/E YYYYMMDD_HH:MM:SS	Specifies an automatic shutdown time. When this time is reached, PSSDIAG will automatically stop data collection and shut itself down. The date and the time can be specified together or separately. For example, you can specify the time only or the date only.
/G	Generic mode. PSSDIAG defaults to a SQL Server-centric data collection mode that requires a running instance of SQL Server. The /G parameter disables SQL Server-specific data collection, so that PSSDIAG can be used for other scenarios.

Automatically starting and stopping PSSDIAG

Sometimes, it may be convenient to have PSSDIAG automatically start data collection at a specified time, or automatically stop after collecting data for a specified time. For example, you may be troubleshooting a problem that consistently appears at 2:00 am. In a case like this, you may want PSSDIAG to start data collection at 1:00 am, and to automatically shut down at 3:00 am. The easiest way to start and stop data collection automatically at a specified time is to use the /B and the /E command-line parameters. Make sure to use the exact date format for these parameters that is specified in the "PSSDIAG Command Line Parameters" section. The times must be specified relative to local time on the computer where PSSDIAG is running.

PSSDIAG will also shut down automatically whenever it finds a file named Pssdiag.stop in the utility's output folder. This can be useful for situations when you want to programmatically shut down PSSDIAG after some event occurs, but you do not know in advance the time that this event will occur. The contents of the Pssdiag.stop file are irrelevant. One option is to use a command like the following in a batch file:

ECHO abc > F:\PSSDIAG\Output\PSSDIAG.STOP

Performance impact of PSSDIAG

Because PSSDIAG is just a wrapper around other data collection APIs and utilities, the performance impact of running PSSDIAG is generally equal to the impact of the traces that PSSDIAG has been configured to capture. The same performance impact would be seen if the same trace data was captured manually, without using PSSDIAG.

PSSDIAG can be configured to capture a small amount of data or a large amount, and the type of data that is captured is typically customized for each incident. Because of this, it is not possible to make a general statement about the effect that running PSSDIAG may have without taking into account the log types and trace events that are being collected. If you are concerned about the potential impact of data collection on a server, contact the Support Professional that sent you PSSDIAG to clarify the diagnostic types that it has been configured to capture.

The one task that Pssdiag.exe performs directly that may consume significant CPU resources is the automatic compression of data files in CAB archives. By default, this feature is disabled. However, it can be enabled with the /C command-line parameter. The /C command-line parameter is discussed in detail in the "PSSDIAG Command Line Parameters" section.

Running PSSDIAG remotely or on a clustered SQL Server

For PSSDIAG to collect data from a remote server or from a clustered instance of SQL Server, the Pssdiag.ini file must be modified. It must specify the name of the server that PSSDIAG should connect to. Tell the Support Professional you are working with the name of the server so that this can be configured correctly before PSSDIAG is sent to you.

If you are making this change yourself, locate the Pssdiag.ini file in the same folder as Pssdiag.exe. Open Pssdiag.ini in Notepad. The first line in the file contains the string "[.]". Replace the period between the square brackets with the remote server's name. If you are collecting data from a named instance of SQL Server, note that the server name is not the full name of the instance of SQL Server. For example, if the name of your instance of SQL Server is "MYSERVER\MyInstance", you will replace the first line of the Pssdiag.ini file with "[MYSERVER]".

SQL Profiler tracing is always performed on the server. Because of this, if PSSDIAG has been configured to collect Profiler data, there are additional complications involved with running PSSDIAG remotely. In these cases, Microsoft recommends that you run PSSDIAG locally on the server. If you want to consider the option of remote data collection when Profiler traces are required, ask the Support Professional you are working with for more details. Again, the performance impact on SQL Server can be visible if this choice is made.

If the instance of SQL Server is clustered, use the virtual server name instead of the computer name of a cluster node.

Important You must always explicitly specify the virtual server name in the Pssdiag.ini file when you connect to a clustered instance of SQL Server, even when PSSDIAG will be run locally on one of the cluster nodes.

↑ Back to the top

The PSSDiag utility was originally created for SQL Server 7.0 and SQL Server 2000. A version was created and included with SQL Server 2005 and later builds. The new version is named SQLDiag and is included with SQL Server. For more information about SQLDiag, see the "SQLDiag Utility".

↑ Back to the top