SQLDiag for SQL Server 2005

The SQLDiag utility on SQL Server 2005 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.

NOTE: These instructions are only suitable for SQL Server 2005 (not SQL Server 2000)

SQLDiag Features:

SQLDiag can collect the following types of diagnostic 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...

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

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

All these files can be found in \Program Files\Microsoft SQL Server\90\Tools\Binn\
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.

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.
EXEC master..xp_cmdshell 'SQLDIAG START'

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.
EXEC master..xp_cmdshell 'SQLDIAG STOP'

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.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Start_SQLDiag]
AS
BEGIN
	--Start SQLDIAG.
 	EXEC master..xp_cmdshell 'SQLDIAG START'
END
GO
Then run the following to set this stored procedure to run when SQL Server starts.
EXEC sp_procoption N'[dbo].[usp_Start_SQLDiag]', 'startup', '1'
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.

Performance Monitor (Perfmon):

The collection of Performance Monitor data (Perfmon) can be enabled by changing the enabled flags from false...
<EventlogCollector enabled="false" startup="false" shutdown="true" />
<PerfmonCollector enabled="false" pollinginterval="5" maxfilesize="256">
to true... 
<EventlogCollector enabled="true" startup="false" shutdown="true" />
<PerfmonCollector enabled="true" pollinginterval="5" maxfilesize="256">
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... 
<ProfilerCollector enabled="false" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350">
to true... 
<ProfilerCollector enabled="true" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350">
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...
SELECT * 
FROM fn_trace_gettable('D:\SQLDIAG\SERVERNAME__sp_trace_nnn.trc', 1)
WHERE TextData is not null

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... 
<BlockingCollector enabled="false" pollinginterval="5" maxfilesize="350"/>
to true... 
<BlockingCollector enabled="true" pollinginterval="5" maxfilesize="350"/>
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 by default. It can also be found in the Windows 2000 Resource Kit.
SET SQLDiagPath=D:\SQLDIAG

CD /D %SQLDiagPath%

echo %date% %time% ---------------------------------- >> DeleteOldFiles.log
echo Delete BLG files >> DeleteOldFiles.log

forfiles -p %SQLDiagPath%\ -m *.blg -d -2 -c "cmd /c echo @FILE @FDATE @FTIME >> DeleteOldFiles.log"
forfiles -p %SQLDiagPath%\ -m *.blg -d -2 -c "cmd /c del /q @FILE"

echo Delete TRC files >> DeleteOldFiles.log

forfiles -p %SQLDiagPath%\ -m *sp_trace*.trc -d -2 -c "cmd /c if not 
	@FILE==%computername%__sp_trace_blk.trc echo @FILE @FDATE @FTIME >> DeleteOldFiles.log"
forfiles -p %SQLDiagPath%\ -m *sp_trace*.trc -d -2 -c "cmd /c if not 
	@FILE==%computername%__sp_trace_blk.trc del /q @FILE"
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:

Tips: