Hi Daksina

the 4 stored procedures you need are

SP_TRACE_CREATE

SP_TRACE_SETEVENT

SP_TRACE_SETFILTER

SP_TRACE_SETSTATUS

also the functions

fn_trace_gettable

fn_trace_getinfo

to generate a trace definition use the following syntax

declare @traceidout int

declare @maxfilesize bigint

declare @on bit

set @on = 1

set @maxfilesize = 50

exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULL

exec sp_trace_setevent @traceidout, 12, 1, @on

exec sp_trace_setevent @traceidout, 12, 3, @on

change @maxfilesize to whatever value you require (its in MB)

Dont append the .TRC to the path above it does it for you.

refer to BOL for all trace events and columns

set a filter using the following syntax

exec sp_trace_setfilter @traceidout, 3, 0, 0, 7

use the following to get your trace details and ID

select * from ::fn_trace_getinfo(default)

use the following to start, stop and close the trace

(must stop a trace before it can be closed. Must be closed before you can access the file)

exec sp_trace_setstatus TRACEID, 1 --start trace

exec sp_trace_setstatus TRACEID, 0 --stop trace

exec sp_trace_setstatus TRACEID, 2 --close trace

finally to output to a table stop and close the trace then use the following syntax

SELECT * INTO temp_trc

FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)

You can create SQL jobs and schedule them using the code above. As it runs server side there are no I\O nasties that a client would generate and you can schedule it at will

😎 😉