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:
For Microsoft SQL Server 2000 and for Microsoft SQL Server 7.0, the following file is available for download from the Microsoft Download Center:
Download the package now.
Collapse this imageExpand this image
Release Date: September 29, 2004
For Microsoft SQL Server 2005 and later versions, use the Pssdiag and Sqldiag manager that can be found on the following codeplex site:
For more information, visit the following MSDN website:
For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:
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
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
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.
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:
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.
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
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
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
(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.
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 /?
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.
Collapse this tableExpand this table
|/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.|
|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.|
|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
|/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.
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
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.
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.