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

ID: Q194860

The information in this article applies to:

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:

  1. 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)


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

    1. 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.


    2. Give the new task the following properties:
      
         Name:     Audit Trace
         Type:     TSQL
         Database: Master
         Command:  Exec sp_audit_trace @directory = '&lt;directory&gt;'
         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

Last Reviewed: April 13, 1999