VirtualObjectives

SQLDiag for SQL Server

The SQLDiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLDiag to collect logs and data files from SQL Server, and use it to monitor your servers or troubleshoot specific problems with your servers.

This article explains how to use SQLDiag as a Service to get more thorough diagnostics than the standard Profiler traces or Server Side Tracing.

SQLDiag Features:

SQLDiag can collect the following types of diagnostic information:

  • Windows performance logs
  • Windows event logs
  • SQL Server Profiler traces
  • SQL Server blocking information
  • SQL Server configuration information

You can specify what types of information you want SQLDiag to collect by editing the XML configuration file.
There are three types of XML files which by default collect the following information:

SQLDiag.XML
Collects the following...

  • Default trace file (if server option is enabled)
  • SQLDiag output text file (contains sql error logs, configuration information, internal performance counters, the output from several key DMVs and much more)
  • msinfo32.txt
  • sqldumper error log
  • log and control files for the SQLDiag process

SD_General.XML
In addition to what was collected in SQLDiag.XML, SD_General.XML collects the following...

  • Windows Event Logs
  • Perfmon
  • SQL Profiler traces (with less verbose events)

SD_Detailed.XML
In addition to what was collected in SQLDiag.XML, SD_Detailed.XML collects the following...

  • Windows Event Logs
  • Perfmon
  • SQL Profiler traces (including several verbose events such as performance statistics, SQL:BatchStarted and SQL:BatchCompleted events)

Each of the above configuration files collects more detailed information then the one preceding it. Naturally, you will require more disk space on the server to collect this data.

All these XML files can be found in \Program Files\Microsoft SQL Server\nnn\Tools\Binn\
If the XML files are not found then run the following to generate them in the path \Program Files\Microsoft SQL Server\nnn\Tools\Binn\

Setting Up SQLDiag as a Service:

  1. Log into the SQL server as an administrator.
  2. Choose a drive with sufficient space to hold log files. (Example D:\)
  3. Create a directory in that drive called something like "SQLDIAG" under which log files will be created. (Example D:\SQLDIAG\)
  4. Copy the desired XML file from \Program Files\Microsoft SQL Server\90\Tools\Binn\ to the directory created above. The reason I've done this is so that you can customize the XML file. Below I will show you how to customize the configuration file.
  5. Bring up a command prompt and type:
    SQLDIAG /R /I"D:\SQLDIAG\SD_Detailed.XML" /O"D:\SQLDIAG" 
    [/R] = Registers the collector as a service.
    [/I cfgfile] = Sets the configuration file, typically either sqldiag.ini or sqldiag.xml. Default is sqldiag.xml
    [/O outputpath] = Sets the output folder.  Defaults to startupfolder\SQLDIAG (if the folder does not exist, the collector will attempt to create it)
  6. Bring up Services.
  7. You should now see the SQLDIAG service in the list and it is not running.
    NOTE: In a cluster, change the log on user to be an appropriate user, usually the same credentials under which the cluster is running.
If you make a mistake, you may need to un-register the service using SQLDIAG /U  at a command prompt and start again.

Starting the SQLDIAG Service:

  1. Log into the SQL server as an administrator.
  2. To start the service, at a command prompt type SQLDIAG START  
    NOTE: Never start the SQLDIAG service from Services or using NET START. Also setting the SQLDIAG service to start automatically never seems to work correctly. You also cannot specify start parameters in the registered SQLDIAG service. Very annoying!
  3. Look in the output directory (specified in the /O switch) there should now be various files created there.

There is no reason why you could not issue the following query to start SQLDiag, provided that xp_cmdshell is enabled first.

Stopping the SQLDIAG Service:

  1. Log into the SQL server as an administrator.
  2. At a command prompt type: SQLDIAG STOP  

It is recommended not to stop the SQLDIAG service from Services, however, I've had no problems doing this.
Again, there is no reason why you could not issue the following query to stop SQLDiag, provided that xp_cmdshell is enabled first.

Automatically Starting SQLDiag:

SQLDiag is only supposed to be a diagnostic tool which runs for a limited time. However, where needs dictate, SQLDiag is often called into action to help resolve random application issues by running it over a period of weeks or months. This is often the case for development environments where you want to collect the diagnostic data automatically and make it available to developers. Remembering to manually run SQLDiag when a server is restarted is annoying. Here is how you can start SQLDiag automatically when SQL Server starts.

Run the following to create a stored procedure on the master database.
You will need to enable xp_cmdshell before running this.
Then run the following to set this stored procedure to run when SQL Server starts.
To disable the above just change the last parameter for sp_procoption to zero.

Customizing the Data Collection:


The configuration files SQLDiag.XML, SD_General.XML, and SD_Detailed.XML are basically the same in structure. However, as specified previously, they vary in terms of the detail of data they collect.

You can customize this by modifying the XML file. I recommend starting with the file SD_Detailed.XML as it is much easier to turn off features you don't want. You can edit the XML file using a text editor like notepad.

Take note that if you have copied the XML files from other instances then you should ensure that it is tailored to run on your desired instance. Make sure ssver contains the correct value. "9" means SQL 2005, "10" means SQL 2008, while "10.5" means SQL 2008 R2.
By default "*" means all versions of SQL Server.

You can also specify the instance name in Instance name should the server host multiple SQL instances. By default "*" means all instances.
The following shows an example for a default instance.

Performance Monitor (Perfmon):

The collection of Performance Monitor data (Perfmon) can be enabled by changing the enabled flags from false... to true...
You can also change the polling interval and the maximum file size (in megabytes).
Also, you can enable/disable individual performance counters indicated with the key PerfmonCounter and set the enabled flag true or false.
Please note that PerfmonCounter values for the default files SQLDiag.XML, SD_General.XML, and SD_Detailed.XML are identical in each file.

Perfmon data appears as SQLDIAGnnn.BLG files which are written to the output folder specified in the /O switch used when registering the SQLDIAG service. They can be opened using Performance Monitor which can be found under Control Panel + Administrative Tools + Performance.

Profiler Traces:

The collection of Profiler traces can be enabled by changing the enabled flag from false... to true... You can change the polling interval and the maximum file size (in megabytes).
You can also enable/disable individual events collected by the Profiler by toggling the enabled flag from true to false for specific Events grouped under different EventTypes.

SD_Detailed.XML has the most important Events enabled, whilst SD_General.XML has fewer and SQLDiag.XML has fewer still. That is why I suggest working with the SD_Detailed.XML configuration file and turn off the settings you don't require.

Profiler traces appear as SERVERNAME__sp_trace_nnn.TRC files which are written to the output folder specified in the /O switch used when registering the SQLDIAG service.

They can be opened using Profiler. They can even be read directly using fn_trace_gettable like the example shown below...

Enabling the Blocking Collector:

The Blocking Collector records all blocking events and places these into a separate .TRC file called SERVERNAME__sp_trace_blk.trc. Tracing blocking events is a great way to determine problems with an application. It is not enabled by default even in the SD_Detailed.XML configuration file, however they will appear in the normal trace files which are harder to find. I strongly recommend turning this feature on because it gathers all the Blocked Process Reports into one file which makes it much easier to read.

To enable the Blocking Collector, change the enable flag from false... to true... The Blocking Profiler trace can be found in the file SERVERNAME__sp_trace_blk.trc which is written to the
output folder specified in the /O switch used when registering the SQLDIAG service. It can be accessed in the same way as the other Profiler traces above.

File Maintenance:


The files produced by SQLDiag are not "cleaned up" automatically. SQLDiag will keep collecting data and adding new files until SQLDiag service is stopped. When the service is stopped or started these files are still not removed. You will need to put in place some mechanism to delete old files, especially when running SQLDiag for long periods of time, otherwise you will run out of disk space on your server.

The following batch file can be used to clean up files of a particular type which are older than a specified number of days. This batch file can then be copied to the SQL Server and executed on a regular basis via a SQL Agent Job.

The batch file uses a utility called FORFILES which is on Microsoft Windows Server 2003 and 2008 by default.
Please Note: The last four lines of the batch file above are split for display purposes only. Each line starting with "forfiles" should continue on the SAME line.

Just change the path for SQLDiagPath to the path where the .BLG and .TRC files are located.
The -M switch indicates the file types to operate on. In this example, I am removing both .BLG and .TRC files.
The switch -D is currently set for -2 days. Which means that it will delete any matching file where the modified date is older than 2 days.
The -C switch is the command which will be performed on each matching file.

The batch file will also create a log of the files deleted. The log file is called DeleteOldFiles.log.

For more information on how to use FORFILES please consult...
http://technet.microsoft.com/en-us/library/cc753551%28WS.10%29.aspx


Troubleshooting:

  • These instructions are only suitable for SQL Server 2005 and above (not SQL Server 2000)
  • The examples provided only refer to SQLDiag running as a Service. Other switches within SQLDiag allow it to run in a command prompt and even start and stop automatically. These options are not discussed here.
  • Check the SQLDIAG service under Services to see if it is present.
  • Check the Event Log for error messages.
  • Various text/logs files are created when SQLDiag starts and stops. These may provide more information about the SQLDiag collection process and can be used to diagnose issues with SQLDiag.

Tips:

  • Setting the SQLDIAG service to start automatically appears not not collect data correctly. Even using NET START may not work. Instead, to start the service, at a command prompt type SQLDIAG START  
  • To correct any mistakes with switches or incorrect paths, you need to first un-register the service using  SQLDIAG /U  at a command prompt before correcting your mistake.
  • When making any changes to the configuration file, you will need to restart the SQLDIAG service in order for the changes to take effect.
  • When the SQLDiag service is restarted, the file numbering of collected .BLG and .TRC files begins again at 1 effectively overwriting the existing file.
  • Profiler trace files are somewhat cached, so new .TRC files may appear empty (with zero length) even when SQLDiag has been running for a while. This is flushed (written) to disk when SQLDiag needs to (i.e. performed internally by SQLDiag). The file is also flushed to disk when SQLDiag is stopped.