RE: Validating trace file events

SSC Guru

Points: 323504

March 8, 2013 at 1:29 pm

Tripped over this same issue myself, even wrote an article about it long ago;

this is a much-improved-since-that-article version of a proc I've created this procedure named "sp_ScriptAnyTrace"

which scripts out any trace to make it portable and also well documented by commenting Every event , every column captured, and every filter.

I consider this a must have in the TSQL toolbox.

--#################################################################################################

--sp_ScriptAnyTrace

-- a procedure to duplicate any trace on a given server, with the added benefit of

--adding comments to make the output much more readable

--and understandable to determine what a given trace was actually doing.

--#################################################################################################

--usage: for any given traceId on your server, pass the trace ID:

--EXEC sp_ScriptAnyTrace 1

--EXEC sp_ScriptAnyTrace 2

--EXEC sp_ScriptAnyTrace 3

--find the traces you have with this:

--select * from sys.traces

--drop procedure sp_ScriptAnyTrace

--#################################################################################################

CREATE PROCEDURE [dbo].[sp_ScriptAnyTrace](@traceid INT)

AS

BEGIN

SET NOCOUNT ON

DECLARE @Results TABLE(

ResultsId INT IDENTITY(1, 1),

ResultsText VARCHAR(MAX))

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

UNION ALL

SELECT '-- Scripting trace_id ' + convert(varchar,@traceid) + ' from server ' + @@servername

UNION ALL

SELECT '-- Trace Last Started/Restarted on ' + DATENAME(dw,start_time) + ' ' + CONVERT(VARCHAR,start_time,111) + ' - ' + CONVERT(VARCHAR,start_time,114) from sys.traces where id=@traceid

UNION ALL

SELECT '-- Scripted for Review/Analysis on ' + DATENAME(dw,getdate()) + ' ' + CONVERT(VARCHAR,GETDATE(),111) + ' - ' + CONVERT(VARCHAR,GETDATE(),114)

UNION ALL

SELECT '--#################################################################################################'

INSERT INTO @Results

(ResultsText)

SELECT '--declare variables for parameterizing the command '

UNION ALL

SELECT ' declare @traceidout int '

UNION ALL

SELECT ' declare @myoptions int '

UNION ALL

SELECT ' declare @mypath nvarchar(256) '

UNION ALL

SELECT ' declare @mymaxfilesize bigint '

UNION ALL

SELECT ' declare @mymaxRolloverFiles int '

UNION ALL

SELECT ' declare @mystoptime 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 @mymaxfilesize = ' + 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 @mymaxRolloverFiles = ' + 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 @mystoptime = ' + 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 @myoptions = ' + CONVERT(VARCHAR, (2 * is_rollover) + (4 * is_shutdown)) + ' -- TRACE_FILE_ROLLOVER = ' + CASE

WHEN is_rollover = 1

THEN 'TRUE'

ELSE 'FALSE'

END + ', SHUTDOWN_ON_ERROR = ' + CASE

WHEN is_shutdown = 1

THEN 'TRUE'

ELSE 'FALSE'

END

FROM sys.traces

WHERE id = @traceid

INSERT INTO @Results

(ResultsText)

SELECT '--This is the Actual Path on the scripted server.' UNION ALL

SELECT ' SELECT @mypath = ''' + CASE

WHEN PATH IS NULL

THEN 'C:\Data\mytrace'

ELSE LEFT(PATH, LEN(PATH) - 4)

END + '''' + ' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting '

FROM sys.traces

WHERE id = @traceid UNION ALL

SELECT ' ' UNION ALL

SELECT '--for Portability reasons, we change this in the script right after this actual path to get the path for the default trace, which we assume to exist on the server.' UNION ALL

SELECT ' ' UNION ALL

SELECT ' SELECT @mypath = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX(''\'',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1'

FROM sys.traces

WHERE id = @traceid UNION ALL

SELECT ' SELECT @mypath = @mypath + N''' + REPLACE(REVERSE(LEFT(REVERSE(ISNULL(path,'C:\Data\myprofilertrace')),CHARINDEX('\',REVERSE(ISNULL(path,'C:\Data\myprofilertrace'))) -1)),'.trc','') + ''' --system appends .trc automatically for the filename '

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

UNION ALL

SELECT ' --create the trace '

--if no rollover , do not include the @mymaxRolloverFiles

IF EXISTS(SELECT 1 FROM sys.traces WHERE is_rollover = 1 AND id = @traceid)

BEGIN

INSERT INTO @Results

(ResultsText)

SELECT ' exec sp_trace_create @traceid = @traceidout output, ' UNION ALL

SELECT ' @options = @myoptions, ' UNION ALL

SELECT ' @tracefile = @mypath, ' UNION ALL

SELECT ' @maxfilesize = @mymaxfilesize, ' UNION ALL

SELECT ' @stoptime = @mystoptime '

END

ELSE

BEGIN

INSERT INTO @Results

(ResultsText)

SELECT ' exec sp_trace_create @traceid = @traceidout output, ' UNION ALL

SELECT ' @options = @myoptions, ' UNION ALL

SELECT ' @tracefile = @mypath, ' UNION ALL

SELECT ' @maxfilesize = @mymaxfilesize, ' UNION ALL

SELECT ' @stoptime = @mystoptime, ' UNION ALL

SELECT ' @filecount = @mymaxRolloverFiles '

END

--details

INSERT INTO @Results

(ResultsText)

SELECT ''

INSERT INTO @Results

(ResultsText)

SELECT '--#################################################################################################'

UNION ALL

SELECT ' --Begin Event definitions '

UNION ALL

SELECT '--#################################################################################################'

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

+ SPACE(74 - LEN(' exec sp_trace_setevent @traceidout,'

+ CONVERT(VARCHAR(MAX), X.eventid)

+ ','

+ CONVERT(VARCHAR(MAX), X.columnid)

+ ',@on '))

+ '--' + ISNULL(E.Name, '') + ',' + ISNULL(V.name, '')

FROM ::fn_trace_geteventinfo(@traceid) 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 ''

INSERT INTO @Results

(ResultsText)

SELECT '--#################################################################################################'

UNION ALL

SELECT ' --End Event definitions '

UNION ALL

SELECT '--#################################################################################################'

INSERT INTO @Results

(ResultsText)

SELECT ''

INSERT INTO @Results

(ResultsText)

SELECT '--#################################################################################################'

UNION ALL

SELECT '--begin filter definitions'

UNION ALL

SELECT '--#################################################################################################'

INSERT INTO @Results

(ResultsText)

SELECT ''

INSERT INTO @Results

(ResultsText)

SELECT '-- WHERE 1 = 1'

INSERT INTO @Results

(ResultsText)

SELECT

'-- '

+ CASE

WHEN logical_operator = 0

THEN ' AND '

ELSE ' OR '

END

+ ISNULL(V.name, ' NULL')

+ 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

+ CASE

WHEN VALUE IS NULL

THEN ' NULL '

--these are the(varchar) columns in the definition

WHEN X.columnid IN ( 6, 7, 8, 10,

11, 26, 34, 35,

36, 37, 38, 39,

40, 42, 45, 46,

47, 59, 64 )

THEN ' N''' + CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ''' '

--these are the int/bigint columns in the definition

WHEN X.columnid IN ( 3, 4, 5, 9,

12, 13, 16, 17,

18, 19, 20, 21,

22, 23, 24, 25,

27, 28, 29, 30,

31, 32, 33, 44,

48, 49, 50, 51,

52, 53, 55, 56,

57, 58, 60, 61,

62, 66 )

THEN ' ' + CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ' '

--image/ntest/datetime/uniqueidentifier columns (1,2,14,15,41,43,54,63,65)

ELSE ' N''' + CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ''' '

END

FROM ::fn_trace_getfilterinfo(@traceid) X

INNER JOIN sys.trace_columns V

ON X.columnid = V.trace_column_id

INSERT INTO @Results

(ResultsText)

SELECT ' exec sp_trace_setfilter @traceidout'

+ ','

+ CONVERT(VARCHAR, X.columnid)

+ ','

+ CONVERT(VARCHAR, logical_operator)

+ ','

+ CONVERT(VARCHAR, comparison_operator)

+ ','

+ CASE

WHEN VALUE IS NULL

THEN ' NULL '

--these are the(varchar) columns in the definition

WHEN X.columnid IN ( 6, 7, 8, 10,

11, 26, 34, 35,

36, 37, 38, 39,

40, 42, 45, 46,

47, 59, 64 )

THEN ' N''' + CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ''' '

--these are the int/bigint columns in the definition

WHEN X.columnid IN ( 3, 4, 5, 9,

12, 13, 16, 17,

18, 19, 20, 21,

22, 23, 24, 25,

27, 28, 29, 30,

31, 32, 33, 44,

48, 49, 50, 51,

52, 53, 55, 56,

57, 58, 60, 61,

62, 66 )

THEN CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ' '

--image/ntest/datetime/uniqueidentifier columns (1,2,14,15,41,43,54,63,65)

ELSE ' N''' + CONVERT(VARCHAR(8000), ISNULL(VALUE, ' NULL')) + ''' '

END

FROM ::fn_trace_getfilterinfo(@traceid) X

INNER JOIN sys.trace_columns V

ON X.columnid = V.trace_column_id

INSERT INTO @Results

(ResultsText)

SELECT '--#################################################################################################'

UNION ALL

SELECT '---end filter definitions'

UNION ALL

SELECT '--#################################################################################################'

INSERT INTO @Results

(ResultsText)

SELECT ''

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!