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

See this article by Brian Moran where I got the inspiration to try this one out …

Handy Scripts

Some handy scripts :

Script to display running traces
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)

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

Retrieve trace file data using ::fn_trace_gettable function.
select * 
  from ::fn_trace_gettable( 'E:\\mssql\\admin\\iNYC01\\audit\\AUD_TSQLSTMT_2006-11-14.trc', default)

select * 
  into t_trace 
  from ::fn_trace_gettable( 'E:\\mssql\\admin\\iNYC01\\audit\\AUD_TSQLSTMT_2006-11-14.trc', default)

URI’s from the documentation

One thought on “SQL Trace Reference

  1. allamiro says:

    Excellent article but how do you change the file size limits or the properties for the C2 audit its 200 MB

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>