How to Create and Start SQL Server Trace Automatically

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