Starting SQLDIAG or PSSDIAG automatically using SQL Alerts

This is something I was thinking about this week whilst chatting with a customer. I've done it before on occasions, but never really documented it. Consider the fact that you have a problem which occurs extremely intermittently, let's say on average every 3 months for the sake of argument. Based upon your initial analysis you decide you need to collect a SQLDIAG when the problem occurs, but for reasons specific to your environment, you do not want to employ the long term monitoring capabilities of SQLDIAG, such as "continuous mode" (/L switch), running a service node (/R and /U switches to register or unregister). So the question is that you want to start the collector when a condition is detected, let's say, a blocking chain occurs.

I've just implemented this in a test system using the following methodology and without installing the SQLDIAG as a service:

1. Configure your SQLDIAG as per your data capture requirements.

2. Create a new alert, in this scenario I was looking for blocking chains, so I set it with the following properties

Type - SQL Server Performance Condition Alert
Object - ServerName:General Statistics
Counter - Processes Blocked
Alert if counter - rises above 5
Options - delay between responses = 60 minutes (this one is optional but just stops you creating lots of multiple sessions and filling up disk space etc - alternatively you could just set the alert to disabled as soon as it has run once)

3. For the response to the alert, create a new job

4. This is a job which will start SQLDIAG from a cmdexec session, name it as such

5. Create a new step, type = Operating system (cmdexec) - I used the following script

start /min cmd.exe /C"sqldiag /E +00:20:00 /Q"

bear in mind that the SQLDIAG directory is in my path variable, so you might alternatively have to type in the full executable location, which considering its length is why I have it as a path variable!

This string translates as, start sqldiag in quiet mode, meaning that it will automatically overwrite previous output in the default output directory and shut it down after a time span of 20 minutes. You can change the switches you use (of and of course what data you actually collect) using the standard SQLDIAG methodology.

6. Confirm and save everything and away you go.

This particular example is easy to test as you can just create a simple blocking chain on a test table, and watch the collector start up. You can open the sqldiag.log or the console.log files from the output directory and watch its progress.

This is not the only or the definitive way to do this, for example I have seen people register the collector as a service, and then get their monitoring software to issue a net start command. However it's one way to do it and keep it completely within the boundaries of SQL Server.

(It also works for PSSDIAG if you're working with us using our internal version.)