Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reverse Engineering a Server Side Trace

By Lowell Izaguirre,

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.

definition of sp_trace_create
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]

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:

select * from :: fn_trace_getinfo(1)
traceid  property  value 
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_96.trc 
20 
NULL 

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:

select path,max_size,stop_time,max_files from sys.traces
path  max_size  stop_time  max_files 
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_96.trc  20  NULL 

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:

select * from ::fn_trace_geteventinfo(1)
eventid  columnid 
18 
18 
18 
18 
740 more rows of integers 

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:

select
X.eventid,
E.Name as EventName,
X.columnid,
V.name as ColumnName
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
eventid  EventName  columnid  ColumnName 
18  Audit Server Starts And Stops  NTUserName 
18  Audit Server Starts And Stops  NTDomainName 
18  Audit Server Starts And Stops  HostName 
18  Audit Server Starts And Stops  ClientProcessID 
more... 

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:

columnid  ColumnName  logical_operator  comparison_operator  value  Description 
10  ApplicationName  0 SQLT%  -- AND ApplicationName LIKE SQLT% 
10  ApplicationName   MS%  -- AND ApplicationName LIKE MS% 
11  LoginName  joe  -- AND LoginName = joe 

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
Total article views: 1572 | Views in the last 30 days: 2
 
Related Articles
FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

fn_trace_gettable Permissions Issue

Selecting trace date remotely

FORUM

Insert combo in table

Insert into with values and select results

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

DYNAMIC SELECT & INSERT QUERY HELP

DYNAMIC SELECT & INSERT QUERY HELP

Tags
scripting    
trace    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones