Manually Tracing in SQL Server

Most DBA’s tend to use Profiler, but sometimes its not feasible to be available to run the trace or for whatever reason it needs to be scheduled.

Scheduling a Trace

Is fairly straightforward. Set up the trace as normal in Profiler, selecting all columns and filters needed. Start and stop the trace. Then go to File | Export | Script Trace Definition | SQL Server 2005

For demonstration purposes, i created a profiler trace using the Duration template, and then added a 15000 millisecond filter on duration, as well as file rollover every 5mb and set it to finish tracing. This is produced like below:

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 27/03/2012  15:57:19         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

SET @DateTime = DATEADD(hr,1,GETDATE())
set @maxfilesize = 5

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

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 15000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Amend the main line for the sp_create_trace, replacing the InsertFileNameHere with the folder location and file name. Putting the .trc extension is not neccessary as this is auto generated.

exec @rc = sp_trace_create @TraceID output, 0, N'C:\MyTrace\LongRunningSQL', @maxfilesize, @Datetime

The second parameter for sp_trace_create, in this case 0, controls whether file rollover is enabled. When the script is auto generated this option does not script out so to enable file rollover, manually change this to 2.

exec @rc = sp_trace_create @TraceID output, 2, N'C:\MyTrace\LongRunningSQL', @maxfilesize, @Datetime

This value is calculated uses the options below:

[ @options= ] option_value

Specifies the options set for the trace. option_value is int, with no default. Users may choose a combination of these options by specifying the sum value of options picked. For example, to turn on both the options TRACE_FILE_ROLLOVER and SHUTDOWN_ON_ERROR, specify 6 for option_value.

More detail here

Then copy the amended syntax into a scheduler job and set a start time and enable.

How do i know if my trace has started?

Either check to see if the initial trace file has been created, or run the following SQL:

SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

which shows all currently running traces

How do I stop a trace?

Fill in the question mark using the trace id from the select query, then execute SQL.

DECLARE @TraceID int
SET @TraceID = ?

--get trace id using
--SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2