Running SQLDiag as Agent Job


This was the discussion of last evening, which was started after we receive a similar request from a specific team. The team wanted to start this trace by themselves without involving the DBAs. One of the possibility was to run SQLDiag as a Windows service; however the requirement in hand is to run it as a SQL Agent  job.

In case you wanted to read about this utility & it’s additional parameters, please refer the below page from SQL Server Books Online. The following parameter helps in the initial setup of SQLDiag.

SQLDiag.exe /E +00:20:00 /N 2 /Q /O E:\temp\SQLDiag_Output

As I tested the above script, SQLDiag utility was started & collected data for 20 minutes before stopping. Since the script was running successfully, the next step was to run it as a SQL Agent job.

I created a job on my lab instance to run SQLDiag & provided Read & Write permission to the SQL Server Agent account on the SQLDiag output folder. On the Job Step properties page, select Operating system (CmdExec) as the type & add the below script. In case of my lab environment, the SQLDiag output will be saved in E:\temp\SQLDiag_Output folder.

image

The default traces, system configuration files are available in the output folder. There will be an additional folder named internal present inside the output folder. The internal folder contains all the output & log files generated by SQLDiag.

image

Hope, this will suffice the need. Happy Learning !!

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Automation in SQL Server, How To, Just Learned, SQL Server Tools and tagged , , . Bookmark the permalink.

One Response to Running SQLDiag as Agent Job

  1. Pingback: SQLdiag | howdoisql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s