Scheduling trace to automatically start and stop on a SQL Server

For many reasons, we may have to schedule a SQL Server trace to start and stop automatically. While we can find an easy option to define when to stop a trace from SQL Server Profiler, we do not have a direct way to define when and how to start an automated trace.

Here is a simple step by step procedure to start and stop a trace automatically as a SQL Server agent job.

Step 1: Open the profiler and design the trace as per your need. However enable a stop time for the trace (This is the time when the trace will stop automatically). Also make sure to save the trace output as a file. Your trace design should look similar to below picture (of-course Trace name, template etc will be different)

Step 2: Start the trace and pause it immediately. Then export the trace definition file as a SQL Script as per below picture.

Step 3: Edit the SQL Script to define where the trace file will be stored. You will find below comments at almost beginning of the script:

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

Immediately after these comments, put the first file name where trace data will be written as below:

exec @rc = sp_trace_create @TraceID output, 0, N'S:\TraceOP\May42010Trace.trc',

@maxfilesize, @Datetime

Here for example I am using first file name as S:\TraceOP\May42010Trace.

Step 4: Create a SQL Job adding this script (after modification of Step 3) as a TSQL Step and schedule the job to start when the trace is required to start. This will start the trace automatically at the desired time and trace will be stopped automatically as defined with in the script.