Microsoft KB Archive/194860

= INF: How to Automate SQL Trace by Means of Scheduled Tasks =

Article ID: 194860

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q194860



SUMMARY
You can use the SQL Trace utility in SQL Server 6.5 to monitor all incoming events sent to SQL Server. This article describes how to use the xp_sqltrace extended stored procedure with scheduled tasks to automate the tracing of incoming events to SQL Server. Xp_sqltrace is the procedure that the SQL Trace utility itself calls. Because tracing all incoming events can produce a large trace file, the procedure will allow you to change the output file of the trace automatically and delete the old trace files.



MORE INFORMATION
The steps below walk through setting up xp_sqltrace to capture all incoming SQL Server events. If you want to alter this procedure to apply filtering to the trace, or if you want detailed information on the xp_sqltrace procedure, see the "xp_sqltrace (version 6.5)" topic in SQL Server Books Online.

By default with SQL Trace, only completed events are captured. However, there is an option for you to capture events on a prefilter basis. This enables you to capture events as they are sent to the server, rather than after they are completed. For more information, see below.

To automate the tracing, perform the following steps:

  Use the following script to create the sp_audit_trace procedure:

use master go     create procedure sp_audit_trace @directory varchar(30) = 'C:', @eventfilter varchar(3) = '31' as     declare @month char(2), @day char(2), @hour char(2), @minute char(2), @str varchar(255)

select @month = CASE   WHEN (DATEPART(month, getdate)) < 10 THEN '0' + convert (char(2), DATEPART(month, getdate)) ELSE convert (char(2), DATEPART(month, getdate)) END select @day = CASE   WHEN (DATEPART(day, getdate)) < 10 THEN '0' + convert (char(2), DATEPART(day, getdate)) ELSE convert (char(2), DATEPART(day, getdate)) END select @hour = CASE   WHEN (DATEPART(hour, getdate)) < 10 THEN '0' + convert (char(2), DATEPART(hour, getdate)) ELSE convert (char(2), DATEPART(hour, getdate)) END select @minute = CASE   WHEN (DATEPART(minute, getdate)) < 10 THEN '0' + convert (char(2), DATEPART(minute, getdate)) ELSE convert (char(2), DATEPART(minute, getdate)) END

select @str = "exec master..xp_sqltrace audit, @eventfilter=" + @eventfilter + ", @fulltext=1, @fullfilepath='" + RTRIM(@directory) + "\sqltrace" + @month + @day + "_" + @hour + @minute + ".txt'"

exec (@str)

 Create a scheduled task to execute sp_audit_trace. To do this, perform the following steps:

 In SQL Enterprise Manager, double-click SQL Executive and then click New Task (the upper left button) on the Manage Scheduled Tasks toolbar to create a new task.  Give the new task the following properties:

        Name:     Audit Trace Type:    TSQL Database: Master Command: Exec sp_audit_trace @directory = ' ' Schedule: Recurring -  set the schedule as desired. Where is the directory you want the trace file to go to. For example, if you want the file to go to D:\mydir, you would type the following:

Exec sp_audit_trace @directory = 'D:\mydir'

If you don't specify a directory, it will default to the root of drive C.  

You can schedule the task to execute any time you want the trace to stop and start again with a new file name. It will call the trace file SqltraceMMDD_hhmm.txt, where:

<pre class="fixed_text">  MM = month when the trace was started DD = day when the trace was started hh = hour when the trace was started mm = minute when the trace was started

Each time the procedure is executed, it updates the output file. For example, if you run it at 10:34 AM on October 13 and then run it again at 5:07 PM on October 14, it will create the following two files:

<pre class="fixed_text">  Sqltrace1013_1034.txt Sqltrace1014_1707.txt

The Sqltrace1013_1034.txt file will contain all the commands sent to SQL Server from October 13 at 10:34 AM until the file was changed at 5:07 PM on October 14. The Sqltrace1014_1707.txt file will contain all the commands sent to SQL Server after that time.

By adjusting the frequency the task is executed, you can control the size of the log file and manage the space it takes on your hard disk drive. After the task has been executed to create a new trace file, you can safely delete the older trace files, if you have determined they do not contain information you need.

By default, SQL Trace captures completed events. This allows it to gather performance information for the queries sent to SQL Server. However, in some cases it may be desirable to capture the events before they are completed. To configure the auditing to capture events as they are sent to SQL Server, before they are completed, modify the command for the above task to read:

Exec sp_audit_trace @eventfilter = '115', @directory = ' '

Where is the directory where you want the trace file to go to, as above.

To stop tracing, create a second On Demand scheduled task (repeating Step 2 in the above procedure) with the following information:

<pre class="fixed_text">  Name:     Audit Trace Stop Type:    TSQL Database: Master Command: Exec xp_sqltrace audit, @eventfilter = 0 Schedule: On Demand

When you execute this task, all tracing will be stopped on the server. If you no longer want to trace at all, you will need to disable the original task that you created in Step 2, to ensure that it will not execute in the future and restart the tracing. To disable the task, double-click it in the Manage Schedule Tasks window and clear the Enabled check box.

Additional query words: filename SEM

Keywords: kbhowto kbinfo KB194860

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.