Use filters to keep your traces meaningfull

Say you have an sql trace for the event ‘TSQL SQL:StmtCompleted’ you are, most probably, NOT interested in the sql statements executed through the SQL Server Agent for instance. Filters below remove most of the unwanted ‘internal’ tsql. …

The contents of your tracefile will become more meanigfull and not unimportant small … …

Filter out ‘internal’ sql server proceses Filter on ‘ApplicationName’
-- --
-- ApplicationName

-- SQL Profiler sessions
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- SQL executed by the SQL Agent
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLDMO_1%'

Filter on ‘LoginName’
-- --
-- LoginName

exec sp_trace_setfilter @TraceID, 11, 0, 7, N'remivisser'
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'<username>'
Read more →

SQL Trace Reference

Display/Alter running traces or an alternative way to shutdown your server (when ‘c2 audit mode’ is enabled). …

Display running traces

When ‘c2 audit mode’ is enabled you will see a trace running at all times. Whenever the server can’t write to it’s trace file it will shut itself down. (You need to restart with the -f switch to fix your problem). …

Example below first displays all running traces and next aborts the trace ‘facilitating’ the ‘c2 audit mode’ option. The following functions are used, ::fn_trace_getinfo (displays information about runing traces) and sp_trace_setstatus (used to alter the status of a trace). …

1> exec sp_configure "c2 audit mode"
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 c2 audit mode                                 0           1            1           1

1> select traceid, property, cast(value as char(64)) as value from ::fn_trace_getinfo(0)
2> go
 traceid     property    value
 ----------- ----------- ------------------------------------------------------------------------
Read more →