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
----------- ----------- ------------------------------------------------------------------------
1 1 6
1 2 C:\\Program Files\\Microsoft SQL Server\\MSSQL\\data\\audittrace_200609061836
1 3 200
1 4 NULL
1 5 1
(5 rows affected)
Alter status of a running trace
To alter the status of a trace, eg starting/stopping it you use the sp_trace_setstatus function. This function takes two arguments; @traceid – the id of the trace and @status – the status of the trace, where status is one of the three options below;
0 – Stops the specified trace.
1 – Starts the specified trace.
2 – Closes the specified trace and deletes its definition from the server.
Now watch the results of aborting the trace in an MS SQLServer instance that is running in ‘c2 audit mode’;
1> exec sp_trace_setstatus 1, 0
2> go
[DBNETLIB]ConnectionCheckForData (CheckforData()).
[DBNETLIB]General network error. Check your network documentation.
$
Do not try this at home, the server shuts itself down.
The shutdown seems rather rude as I can not find any information in the mssqlserver logfile as to why it aborted.
select traceid,
case property
when 1 then 'Trace Options'
when 2 then 'FileName'
when 3 then 'MaxSize'
when 4 then 'StopTime'
when 5 then 'Current Trace status'
end property,
cast(value as char(64)) as value
from ::fn_trace_getinfo( 0)
go
Script below generates commands to stop and close all runing traces
select 'exec sp_trace_setstatus ' + cast( traceid as varchar(8)) + ', 0' + char(10) +
'exec sp_trace_setstatus ' + cast( traceid as varchar(8)) + ', 2'
from ::fn_trace_getinfo(0)
where property = 1
go
select *
from ::fn_trace_gettable( 'E:\\mssql\\admin\\iNYC01\\audit\\AUD_TSQLSTMT_2006-11-14.trc', default)
go
select *
into t_trace
from ::fn_trace_gettable( 'E:\\mssql\\admin\\iNYC01\\audit\\AUD_TSQLSTMT_2006-11-14.trc', default)
go
Excellent article but how do you change the file size limits or the properties for the C2 audit its 200 MB