Reverse Engineering a Server Side Trace

A recent post on SSC caught my eye: How do you reverse engineer a trace? This is not the easiest thing to do, so I thought it might benefit the community as an article. The poster wanted to take the default trace and script it out, as well as other traces on his server. Eventually, he needed the trace scripted out so he could change it so it would not roll over so quickly, and also so he had a longer history of DDL changes than what the default trace captures.

For me, it was pretty common knowledge that you can't change the default trace, but what if you needed to make a copy of that same trace and enhance it so it fit your needs? What the heck is it capturing anyway? In his example, the original DBA also created some additional traces, was long gone, and who knows if those traces were documented anywhere.

It turns out that like most things in SQL Server, the pieces you need are there, but spread about, and not in a single, easy to use package. At the end of the article, you'll have that. When it comes to any trace, there are really three things you need:

1. the basic setup of the trace with it's path, number of rollover files,maximum size of each file, and a stop date if you are planning a limited trace.
2. all the events you plan on tracking.
3. all the filters on the events(if any).

Basic Setup of a Trace

To create a trace, you need to use the Microsoft extended stored procedure sp_trace_create, which takes 6 parameters, but we need just 5 parameters for our purposes.

if you run the query select * from :: fn_trace_getinfo(1) , where 1 is the traceid you want to investigate, you get some nice cryptic results that really don't help you other than send you to Google for an explanation:

For me, that is not useful at all for populating the values needed for scripting a trace...as a matter this data is actually some of the same information available in select * from sys.traces, but formatted by row, with no description.

Getting the information out of sys.traces is a little more understandable:

Since traceid is an OUTPUT parameter , we've located the other 4 parameters we'll need for the final script

Current Events of a Trace

Trace events are added by the Microsoft extended stored procedure sp_trace_setevent, which needs our trace id, an eventid and columnid, and finally an "on" or "off" integer.

To find the current events for an existing trace, Microsoft provides a table value function fn_trace_geteventinfo() to query a specific trace, but once again, the results are not exactly easy to digest and interpret:

This is where your improvements come in, by joining to some of the trace related lookup tables, you can start seeing some much more helpful and intuitive information:

Now we have some results with the two needed integers along with a couple of descriptions that we need for documenting what the events are. I think we have the tools we need to script our events out.

Current Filters of a Trace

Similar to trace events, trace filters are also added by using another Microsoft extended stored procedure sp_trace_setfilter

To get the current filters for our trace, another table function fn_trace_getfilterinfo is used, which unfortunately also returns some pretty obscure and obtuse information. Once again, by joining it against some trace specific tables, as well as some smart interpretation with a case statement, the final results make it much easier to understand:

select 
X.columnid,
 V.name As ColumnName,
logical_operator,
comparison_operator,
value,
 '  -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR ' + V.name  END
+ CASE 
    WHEN comparison_operator = 0  THEN ' = '
    WHEN comparison_operator = 1  THEN ' <> '
    WHEN comparison_operator = 2  THEN '  > '
    WHEN comparison_operator = 3  THEN '  <  '
    WHEN comparison_operator = 4  THEN '  >=  '
    WHEN comparison_operator = 5  THEN ' <=   '
    WHEN comparison_operator = 6  THEN '  LIKE  '
    WHEN comparison_operator = 7  THEN ' NOT LIKE  '
  END   
+ CONVERT(varchar(8000),value) AS Description
from 
::fn_trace_getfilterinfo(1) X 
INNER JOIN  sys.trace_columns V
 ON X.columnid = V.trace_column_id

Now because of the CASE statemnts and the joins, we can get some smart information about the filters that make a lot of sense to us as developers:

With results like that, we have all the basic components we need to make something to script out a trace. We can effectively reverse engineer a trace so we can archive it, change it, or duplicate it on another server. As an added bonus, we can accurately document each line item so we see it's purpose.

here's a brief example of what we might see:

 exec sp_trace_setevent @traceidout,55,6,@on --Hash Warning,NTUserName
 exec sp_trace_setevent @traceidout,55,7,@on --Hash Warning,NTDomainName

and for a filter:

 exec sp_trace_setfilter traceidout,10,0,6, N'SQLT%' -- AND ApplicationName LIKE SQLT%
 exec sp_trace_setfilter traceidout,10,0,6, N'MS%' -- AND ApplicationName LIKE MS%

Here's the finished results: a procedure which takes an integer representing the traceid, and returns a table with either a message stating the trace didn't exist, or the complete suite of SQL statments to recreate the trace as a new trace.

--select * from sys.traces
--drop procedure sp_ScriptAnyTrace
--sp_ScriptAnyTrace 3
CREATE PROCEDURE [dbo].[sp_ScriptAnyTrace](@traceid int)
AS
BEGIN
SET NOCOUNT ON
  declare @Results   TABLE(ResultsId int identity(1,1),ResultsText varchar(max))
--i thought about using a results table, decided i wanted a single varchar max string instead, 
--then had to revernt back to a results table because of concat truncation issues with implicit conversions to varchar(8000)
IF NOT EXISTS(SELECT 1 FROM sys.traces where id = @traceid)
  BEGIN
    insert into @Results(ResultsText) 
      SELECT 'No trace exists with ID = ' + convert(varchar,@traceid)
    SELECT * FROM  @Results order by ResultsID
    RETURN
  END
insert into @Results(ResultsText) 
SELECT '--declare variables for parameterizing the command ' UNION ALL
SELECT 'declare @traceidout       int           ' UNION ALL
SELECT 'declare @options          int           ' UNION ALL
SELECT 'declare @path             nvarchar(256) ' UNION ALL 
SELECT 'declare @maxfilesize      bigint        ' UNION ALL
SELECT 'declare @maxRolloverFiles int           ' UNION ALL
SELECT 'declare @stoptime         datetime      ' UNION ALL
SELECT 'declare @on               bit           ' UNION ALL
SELECT '                                        ' UNION ALL
SELECT 'set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.' 
--script the settings from sys.traces
insert into @Results(ResultsText) 
SELECT 'set @maxfilesize = ' + CASE WHEN max_size IS NULL THEN '20' ELSE CONVERT(varchar,max_size) END + ' --size in MB ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText) 
SELECT 'set @maxRolloverFiles = ' + CASE WHEN max_files IS NULL THEN ' 5 ' ELSE CONVERT(varchar,max_files) END + '  --number of files; ie if 5 files, start rewriting on rollover ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText) 
SELECT 'set @stoptime = ' + CASE WHEN stop_time IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(40),stop_time,121)+ '''' END + '  -- null if never ends, else a specific date ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @options = 2' --rollover, ignore all other settings.
insert into @Results(ResultsText) 
SELECT 'set @path  = '''  + CASE WHEN path IS NULL THEN 'mytrace' ELSE LEFT(path,LEN(path) - 4) END + '''' + '  -- the trace adds ".trc" to the pathname, so avoiad "name.trc.trc" by removing it for scripting ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText) SELECT '' 
--sp_trace_create [ @traceid = ] trace_id OUTPUT 
--, [ @options = ] option_value 
--, [ @tracefile = ] 'trace_file' 
--[ , [ @maxfilesize = ] max_file_size ]
--[ , [ @stoptime = ] 'stop_time' ]
--[ , [ @filecount = ] 'max_rollover_files' ]
insert into @Results(ResultsText) SELECT '  --create the trace '   
insert into @Results(ResultsText) 
SELECT '--create the trace ' UNION ALL
SELECT 'exec sp_trace_create ' UNION ALL
SELECT '    @traceid           = @traceidout output, ' UNION ALL
SELECT '    @options           = @options, ' UNION ALL
SELECT '    @tracefile         = @path, ' UNION ALL
SELECT '    @maxfilesize       = @maxfilesize, ' UNION ALL
SELECT '    @stoptime          = @stoptime, ' UNION ALL
SELECT '    @filecount         = @maxRolloverFiles ' 
--details
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT  '  --for the Event Every SQL statement completed, capture columns of accessible data  ' 
        --exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName    
insert into @Results(ResultsText) 
SELECT '  exec sp_trace_setevent @traceidout,' + CONVERT(varchar(max),X.eventid) + ',' +  CONVERT(varchar(max),X.columnid) + ',@on --'
  +  E.Name + ',' + V.name 
 from  ::fn_trace_geteventinfo(1) AS X
INNER JOIN sys.trace_events E ON X.eventid  = E.trace_event_id
INNER JOIN sys.trace_columns V ON X.columnid = V.trace_column_id
insert into @Results(ResultsText) SELECT '--filters'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) 
select '  exec sp_trace_setfilter  traceidout' + ',' 
+ CONVERT(varchar,X.columnid) + ',' 
+ CONVERT(varchar,logical_operator) + ',' 
+ CONVERT(varchar,comparison_operator) + ',' 
+  ' N''' + CONVERT(varchar(8000),value) + ''' '
+ '  -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR '   END + V.name
+ CASE 
    WHEN comparison_operator = 0  THEN ' = '
    WHEN comparison_operator = 1  THEN ' <> '
    WHEN comparison_operator = 2  THEN '  > '
    WHEN comparison_operator = 3  THEN '  <  '
    WHEN comparison_operator = 4  THEN '  >=  '
    WHEN comparison_operator = 5  THEN ' <=  '
    WHEN comparison_operator = 6  THEN '  LIKE  '
    WHEN comparison_operator = 7  THEN ' NOT LIKE  '
  END   
+ CONVERT(varchar(8000),value) 
from 
::fn_trace_getfilterinfo(@traceid) X 
INNER JOIN  sys.trace_columns V
 ON X.columnid = V.trace_column_id

insert into @Results(ResultsText) SELECT '---final step'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT '--turn on the trace '  
insert into @Results(ResultsText) SELECT '  exec sp_trace_setstatus @traceidout, 1 ---start trace   ' 
insert into @Results(ResultsText) SELECT '  --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it   ' 
insert into @Results(ResultsText) SELECT '  --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it ' 
SELECT * FROM  @Results order by ResultsID
END