During an early career of mine as a database consultant – when I was dealing with SQL Server 2000, I often needed to collect various data related to SQL Server. My favorite tool to collect the data is PSSDIAG tool. It is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. It collects Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collected can be used by SQL Nexus tool which help you troubleshoot SQL Server performance problems. PSSDIAG is 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.
If you are using SQL Server 2000 – you need to seriously consider to upgrading it to SQL Server 2012.
Here is a PSSDIAG Data Collection Utility updated in August 2012. My friend and SQL Server Expert Amit Benerjee have written an excellent article on this subject, I encourage all of you to read the same.
Note: For SQL Server 2012 there is SQLDiag.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Pinal.
That’s a pretty old link just that it was updated today. That PSSDiag is for SQL 2000 or 7.0. Refer to https://support.microsoft.com/en-us/help/830232/pssdiag-data-collection-utility which redirects to the same link you mentioned above.
For SQL 2005+, jackli released PSSDiag last year on codeplex. Can be downloaded from
More details on that release https://techcommunity.microsoft.com/t5/SQL-Server-Support/bg-p/SQLServerSupport
That said, running PSSDiag may not always be a easy on Prod servers. You know the gotchas – approvals, firewalls etc..
I, instead, encourage to use already inbuilt tool called sqldiag. There is a GUI version of it so that you configure easily.
P.S: Any discussion on PSSDiag would seem incomplete without a mention of its co-author Amit Banerjee. Check out his cool tips
Prashant – you are absolutely correct!
This is very old for sure and for those who are still stuck with SQL Server 2000.
I upgraded the link of Amit Benerjee in the blog post – Amit is great friend of mine.
Cool..
Thanks for the mention! :)
:-) Glad to be your friend!
Now WordPress comments need a LIKE button! :)
Got a customer that I tried to run this on. They had installed it remotely on a server, and installed the service, but it would start and stop almost immediately. Why is it doing this?
Any idea if there is any tool like SQLDiag for SQL Server 2012 clusters?
SQLDiag works for SQL 2012 standalone/cluster both. Anything specific you want to capture for clustered installation which SQLDiag doesn’t?
Ref: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms162833(v=sql.110)
Btw, beware of a gotcha
Thanks @Prashant for the reply.
No problem, Pinal!
Hi Prashant,
Thanks for you reply. However I tried both the links and none of them worked. Up till SQL 2008 I used the GUI tool to configure the SQLDiag but since now I have to use the command line, perhaps that is where I am having an issue. I want to capture the traces, all of that information that I used to capture with GUI tool for SQL 2008. I am not sure what I am doing wrong with the links mentioned above by you but both of them did not work for me. :(
@Sunny – If you want UI, you can use https://archive.codeplex.com/?p=diagmanager
Release notes: https://techcommunity.microsoft.com/t5/SQL-Server-Support/bg-p/SQLServerSupport
Diagmanager is a UI for PSSDiag. Works until 2008 R2.
I understand what you’re talking about. SQLDiag UI doesn’t support SQL 2012+ yet. You may rather go for https://archive.codeplex.com/?p=sqlnexus
There are pre-defined set of XML files to collect data with/without traces.
For SQL 2012, I think you might want to replace ssver=”10.50″ with ssver=”11″ in the ..PerfStatsScript2012SQLDiag.XML file though.