DECLARE @TraceID INT
DECLARE @ON BIT
DECLARE @RetVal INT
SET @ON = 1
exec @RetVal = sp_trace_create @TraceID OUTPUT, 2, N'S:\trace\TraceFile'
print 'This trace is Trace ID = ' + CAST(@TraceID AS NVARCHAR)
print 'Return value = ' + CAST(@RetVal AS NVARCHAR)
/*
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @ON -- Textdata
exec sp_trace_setevent @TraceID, 10, 3, @ON -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 6, @ON -- NTUserName
exec sp_trace_setevent @TraceID, 10, 7, @ON -- NTDomainName
exec sp_trace_setevent @TraceID, 10, 11, @ON -- LoginName
exec sp_trace_setevent @TraceID, 10, 12, @ON -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @ON -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @ON -- StartTime
exec sp_trace_setevent @TraceID, 10, 15, @ON -- EndTime
*/
-- 12 = SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 1, @ON -- Textdata
exec sp_trace_setevent @TraceID, 12, 3, @ON -- DatabaseID
exec sp_trace_setevent @TraceID, 12, 6, @ON -- NTUserName
exec sp_trace_setevent @TraceID, 12, 7, @ON -- NTDomainName
exec sp_trace_setevent @TraceID, 12, 11, @ON -- LoginName
exec sp_trace_setevent @TraceID, 12, 12, @ON -- SPID
exec sp_trace_setevent @TraceID, 12, 13, @ON -- Duration
exec sp_trace_setevent @TraceID, 12, 14, @ON -- StartTime
exec sp_trace_setevent @TraceID, 12, 15, @ON -- EndTime
--------------
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'Update%'; --(6 =like ,7= not like)(0=and, 1=or)
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%drop%';
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%delete%';
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'create%';
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%clmns%';
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%SHOW_STATISTICS%';
exec sp_trace_setfilter @TraceID, 11,0, 7, N'%xxx_user%';
/*
You can find the ID for each trace-event, columns, etc from Books Online; just search for the sp_trace_create, sp_trace_setevent and sp_trace_setfiler sprocs. You can then control the trace as follows:
exec sp_trace_setstatus 2, 0 -- Stop the trace,2 is the trace id, can get from sys.traces table
exec sp_trace_setstatus 2, 1 -- Start the trace
exec sp_trace_setstatus 2, 2 -- Close the trace file and delete the trace settings
SELECT * FROM sys.traces
SELECT * FROM fn_trace_gettable( 'S:\trace\TraceFile.trc',default);
*/
