Home → mssql → Archive by category "SQL Trace"

Category Archives: SQL Trace

Or an alternative way to audit … database activity in SQL Server.

/ SQL Trace
# 1923
NOV, 14 2006

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>'

/ SQL Trace
1
# 1927
SEP, 7 2006

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

remidian