set up a server side trace using the following
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 --size in MB's
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.)
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_tablename
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉