INF: How to Automate SQL Trace by Means of Scheduled Tasks
ID: Q194860
|
The information in this article applies to:
-
Microsoft SQL Server version 6.5
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 = '<directory>'
Schedule: Recurring - set the schedule as desired.
Where <directory> 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:
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:
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 = '<directory>'
Where <directory> 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:
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 :
Version : WINNT:6.5
Platform : winnt
Issue type : kbhowto kbinfo
|