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

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 = '<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.