What is PSSDiag?
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. More info at http://support.microsoft.com/kb/830232
How to run PSSDiag?
Once you have fot PSSDiag.exe from Microsoft you may have to perform below steps to run PSSDiag.
1. Copy PSSDiag to E:\PSSDiag (You can copy to any drive which has sufficient space. In this example we will copy to E:\PSSDiag folder)
2. Open command prompt and go to E:\PSSDiag
3. Type PSSDiag.exe and hit enter. This will extract all the required files to E:\PSSdiag
4. Now type pssdiag and hit enter. This will start pssdiag on your server. It takes couple of minutes to initilize this service.
You service will be started when you get prompt like below (mosty in bold green color):
2013/02/02 11:10:12.80 PSSDIAG Collection started. Press Ctrl+C to stop.
5. Now to stop pssdiag just press Ctrl+C
6. Output of PSSDIag will be in Output folder at E:\PSSDiag. This utility will automatically create this folder if it does not exist and will overwrite if old output exists.
NOTE: If for some reason you are not able to access cmd or session is lost and you have to stop the PSSDIiag then just go to Output folder and create sqldiag.stop file from OS and PSSDiag will stop.
Now, the above step will work fine if you want to run pssdiag for small time frame. What to do if you have to run it for 12 hours. In this case you have to create a service for pssdiag and run from service.
How to create PSSDIag as service?
1. Run pssdiag /R after step 3 above.
2. Go to services.msc and you will see new service created as SQLDiag
3. This service will run under local system account by default. If BuiltInAdmin account is disabled then you have to change the account to some different account which has admin access on server as well as SQL Server instance. Best is to use the account under which your SQL Server service is running.
4. Right click on service and click Start to start PSSDiag
5. To stop, right click and hit Stop to stop PSSDiag
How to uninstall PSSDiag service?
Just run pssdiag /U and the service will be removed.
NOTE: Run “PSSDIAG /?” in command line to check all possible parameters that PSSDiag supports and can be used to run PSSDIag from command prompt.