Skip to content

Run PSSDiag to collect logs

Jump to bottom
JosephPilov-MSFT edited this page Feb 14, 2023 · 17 revisions

Prerequisites

  • Pssdiag supports version PowerShell 4.0 and higher

  • Execution policy supported is RemoteSigned or less restrictive

    If you have never run Powershell scripts before on this system, you must ensure that execution Policy allows to run scripts. Otherwise, you will get UnauthorizedAccess error "pssdiag.ps1 cannot be loaded because running scripts is disabled on this system"

    Get-ExecutionPolicy

    If the result is "Restricted", reset it to RemoteSigned or Unrestricted for your user

    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

  • Full Language Mode

    PSSDIAG requires FullLanguage language mode. This mode is on by default.

    $ExecutionContext.SessionState.LanguageMode

    If you get an error like this: Method invocation is supported only on core types in this language mode you need to enable FullLanguage mode

    $ExecutionContext.SessionState.LanguageMode = "FullLanguage"

    For more information see about_Language_Modes

How to run PSSDIAG

  1. Locate your Pssdiag zip file. If you do not have one, here are instructions on how to create one, see Creating PSSDIAG Package

  2. Copy the .zip to the machine where SQL Server is running or the active node if it's a cluster.

  3. Extract the contents of the .zip file to a folder (for example in D:\pssd)

    The folder must meet the following requirements:

    • SQL Server startup/service account has Full Control permission of this folder.
    • The drive should be fast performing and NOT a network-mapped drive or a network share. Preferably a dedicated drive, different from the where databases reside
    • The drive has sufficient disk space for the PSSDIAG data (run PSSDIAG for 1 minute to estimate how much output data is generated)

  4. Open an Admin Windows PowerShell and go to the folder.

    PS D:\>cd .\pssd

  5. Run pssdiag.ps1 from PowerShell

    PS D:\pssd> .\pssdiag.ps1

    Alternatively, you can run the pssdiag.ps1 file from a admin Command Prompt

    cd d:\pssd
d:\pssd> powershell -File pssdiag.ps1

    This will launch a console window which will execute SQLDiag.exe, which ships with SQL Server.

    NOTE: If you do not have SQL Server installed on the system, PSSDIAG cannot execute because it depends on the SQLDiag utility.

  6. Wait for Pssdiag to completely initialize. You will see a message: SQLDIAG Collection started. Press Ctrl+C to stop. once this has happened

  7. Re-create the problem that you want to trace

How to Stop PSSDIAG

  1. To stop PSSDIAG, press Ctrl+C once and wait for it to completely shutdown.

  2. You will see messages like this:

     SQLDIAG Ctrl+C pressed.  Shutting down the collector
 Shutting down the collector
 SQL Base: ClusterLogs UTCLast24Hrs
 SQL Base: DBCC LogInfo
 SQL Base: Errorlog.0
 SQL Base: Errorlog.1
 SQL Base: Errorlog.2
 SQL Base: Errorlog_All
 SQL Server Perf Stats: Perf Stats Snapshot
 SQL Server Perf Stats: Top CPU Query Show Plan XML
 Dumping the Application Eventlog...
 Dumping the Security Eventlog...
 Dumping the System Eventlog...
 SQLDIAG Ending data collection.  Please wait while the process shuts down and files are compressed (this may take several minutes)

    Note: You may be prompted to press "Y" to complete the batch.

    Terminate batch job (Y/N)? y

  3. Zip the \output directory which contains the captured logs.

  4. Copy the zip file to a different machine and/or send for analysis. SQL Nexus can be used for performance analysis.

Advanced scenarios for running PSSDIAG

PSSDIAG.cmd executes a utility called SQLDiag.exe which also ships with SQL Server. Therefore you can call PSSDIAG.ps1 or SQLDiag.exe interchangeably. Therefore, you can find more information on all the scenarios on how to run PSSDAIG/SQLDIAG utility, in this SQLDiag Utility article

More advanced scenarios on running PSSDIAG

Note: PowerShell syntax requires that the parameter name and parameter value are separated with a space. The syntax is in the form -Param pValue. Thus you will notice in the examples: -E +03:00:00 or -N 2, and so on.

Schedule PSSDIAG to start or stop at specific times

  • If you want to run PSSDIAG for a specific amount of time, say for 3 hours you can use -E:

    .\pssdiag.ps1 -E +03:00:00

  • To delay the start of the PSSDIAG collection for a specific amount of time, say 2 hours from now, you can use -B:

    .\pssdiag.ps1 -B +02:00:00

  • To configure PSSDIAG to restart every 3 hours and collect data continuously

    .\pssdiag.ps1 -E +03:00:00 -L -Q

Configure PSSDIAG to collect data in a non-default folder (output_0000x)

.\pssdiag.ps1 -N 2

-N output_folder_management_option Configures to either overwrite or rename 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 output_00001, output_00002, and so on. After renaming the current output folder, SQLdiag writes output to the default \output folder.

Configure PSSDIAG as a Service

To avoid having to be logged on to the console when PSSDIAG runs, you can create a service. The you use the -R switch in combination with the other required switches to create/register the service. Once registered, you will see a new service in the Services applet called SQLDIAG.

  • To configure PSSDIAG as a Service (restart every 1 hours and collect data continuously)

    .\pssdiag.ps1 -R -L -E +01:00:00 -P C:\pssdiag\pssd
.\pssdiag.ps1 start

    NOTE You can use the -P parameter and the folder it points to is the same folder where the PSSDIAG files reside (pssdiag.xml, pssdiag.ps1, etc.) but this is no longer a requirement after version 16.22.11.11

  • To stop the service

    .\pssdiag.ps1 stop

  • To unregister the service

    .\pssdiag.ps1 -U

Service account

By default, PSSDIAG registers itself to run under the LocalSystem account. This account can be changed from the Services applet (services.msc) the same way you'd change the startup account for any service. Note, that PSSDIAG must be run with an account that is both a Windows Administrator on the server and a member of the sysadmin SQL role on the SQL Server instances being monitored. You can do one of the following:

  • Add the LocalSystem account in the Sysadmin role

    ALTER SERVER ROLE sysadmin ADD member [NT AUTHORITY\SYSTEM]

  • Add a service account in the SQLDIAG service that already has Sysadmin privileges

Advanced scenario to stop PSSDIAG

PSSDIAG will shut down automatically whenever it finds a file named sqldiag.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 sqldiag.stop file are irrelevant. One option is to use a command like the following in a batch file to create a file:

ECHO abc > F:\PSSDIAG\Output\sqldiag.stop

Or from PowerShell

Set-Content -Value "stop please" -Path "G:\pssdiag\output\sqldiag.stop"

Alternative PSSDIAG data collection

If for some reason you have issues with running Pssdiag.ps1 or the corresponding SQLDiag.exe, you have 2 alternatives to run the tool.

  1. You can call SQLDiag.exe directly
  2. You can use the ManualStart and ManualStop batch files

Call SQLDiag.exe directly

As mentioned earlier, PSSDIAG facilitates building the command and the configuration files to execute SQLDiag.exe that is installed with SQL Server. If you have an issue with running PowerShell, you can use a command similar to this to invoke SQLDiag.exe. Keep in mind that you may have to adjust the folder name depending on the version of SQL Server installed on your computer (e.g \140\Tools is for SQL Server 2017, \150 is for version 2019 and so on)

C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqldiag.exe /Ipssdiag.xml /Ooutput /PC:\temp\pssd /N1 /C0

Here is a snippet of what PSSDIAG actually does:

image

ManualStart.txt and ManualStop.txt

The PSSDIAG Config Manager tool offers an alternative way to gather most of the needed logs, assuming PSSDIAG was configured for a single instance. When you configure PSSDIAG to collect data from a single instance (Instance Name is not "*" but a specific name), the PSSD.zip file will contain two additional files: ManualStart.txt and ManualStop.txt

These files are to be used in cases where execution of PSSDIAG does not work for some reason. It allows you to manually collect some base information. This includes Perfmon, Perfstat scripts and some other configuration information for the instance (sp_configure, sys.databses, etc) without having to rely on SQLDiag.exe collector. The script is meaningful to use only in cases where you configured log collection for a single instance. This will allow SQLCMD.EXE to connect to the correct instance and run the data collection. If you configured your instances with "*" , meaning all instances, this method of log collection will not work.

How to use

  1. Rename the ManualStart.txt file to ManualStart.cmd by change the extension

  2. Rename the ManualStop.txt to ManualStop.cmd by changing the extension

  3. Execute ManualStart.cmd from a Command Prompt by running:

    ManualStart.cmd

  4. When ready to stop, execute ManualStop.cmd from another Command Prompt window

    ManualStop.cmd

  5. Find the collected data in the \Output folder

Running Pssdiag (previous versions)

  1. Locate your Pssdiag zip file. If you do not have one, here are instructions on how to create one, see Creating PSSDIAG Package

  2. Copy the .zip to the machine where SQL Server is running or the active node if it's a cluster.

  3. Extract the contents of the .zip file to a folder (for example in D:\pssd)

    The folder must meet the following requirements:

    • SQL Server startup/service account has Full Control permission of this folder.
    • The drive should be fast performing and NOT a network-mapped drive. Preferably a dedicated drive, different from the where databases reside
    • The drive has sufficient disk space for the PSSDIAG data (run PSSDIAG for 1 minute to estimate how much output data is generated)

  4. Open an admin Command Prompt and go to the folder.

    d:\>cd pssd

  5. Run pssdiag.cmd from Command Prompt

    d:\pssd>pssdiag.cmd

  6. Wait for Pssdiag to completely initialize. You will see a message: SQLDIAG Collection started. Press Ctrl+C to stop.

  7. Re-create the problem that you want to trace

  8. To stop PSSDIAG, press Ctrl+C once and wait for it to completely shutdown.

  9. You will see messages like this:

    SQLDIAG Ctrl+C pressed.  Shutting down the collector
Shutting down the collector
SQL Base: ClusterLogs UTCLast24Hrs
SQL Base: DBCC LogInfo
SQL Base: Errorlog.0
SQL Base: Errorlog.1
SQL Base: Errorlog.2
SQL Base: Errorlog_All
SQL Server Perf Stats: Perf Stats Snapshot
SQL Server Perf Stats: Top CPU Query Show Plan XML
Dumping the Application Eventlog...
Dumping the Security Eventlog...
Dumping the System Eventlog...
SQLDIAG Ending data collection.  Please wait while the process shuts down and files are compressed (this may take several minutes)

Note: You may be prompted to press "Y" to complete the batch.

   Terminate batch job (Y/N)? y
  1. Zip the \output directory which contains the captured logs.
  2. Copy the zip file to a different machine and/or send for analysis. SQL Nexus can be used for performance analysis.

Advanced scenarios for running PSSDIAG (previous versions)

PSSDIAG.cmd executes a utility called SQLDiag.exe which also ships with SQL Server. Therefore you can call PSSDIAG.CMD or SQLDiag.exe interchangeably.

More advanced scenarios on running PSSDIAG

  • If you want to run PSSDIAG for a specific amount of time, say for 3 hours you can use /E:

    PSSDIAG /E +03:00:00

  • To delay the start of the PSSDIAG collection for a specific amount of time, say 2 hours from now, you can use /B:

    PSSDIAG /B +02:00:00

  • To configure PSSDIAG to restart every 3 hours and collect data continuously

    PSSDIAG /E +03:00:00 /L /Q

For more information on all these scenarios on how to run PSSDAIG/SQLDIAG utility, please review this SQLDiag Utility article

Alternative PSSDIAG execution (previous versions)

If for some reason you have issues with running Pssdiag.cmd and corresponding SQLDiag.exe, the tool offers an alternative way to gather most of the needed logs.

ManualStart.cmd

This batch file is designed to execute all of the T-SQL scripts and Perfmon that PSSDIAG usually collects without having to rely on SQLDiag.exe collector. It simply invokes each script via SQLCMD.EXE or Logman.exe (for Perfmon) while you are reproducing the issue to be captured. The script is meaningful to use only in cases where you configured log collection for a single instance. This will allow SQLCMD.EXE to connect to the correct instance and run the data collection. If you configured your instances with "*" , meaning all instances, this method of log collection will not work. Execute it by double-clicking on the file or running it from command prompt.

ManualStop.cmd

Once you are ready to stop the log collection, you can invoke ManualStop.cmd

ManualStop.cmd

Use this file to stop log collection that was initiated by ManualStart.cmd as well as gather any logs typically configured to be collected on PSSDIAG shutdown. Run it by double-clicking on the file or executing it from command prompt

ManualStop.cmd
Clone this wiki locally