March 13, 2013 at 7:18 am
I'm trying to find what is being traced from a server-side trace on a couple SQL servers I'm managing. The person who created these traces left before I started, and there are no clear notes about this.
The trace logs to a file (figured that out with this bit of T-SQL:
SELECT t.id
,CASE t.status
WHEN 0 THEN 'Stopped'
ELSE 'Running'
END AS status
,t.path
,t.max_size
,t.stop_time
,t.max_files
,t.is_rollover
,t.is_shutdown
,t.is_default
,t.file_position
,t.start_time
,t.last_event_time
,t.event_count
FROM sys.traces AS t
WHERE t.is_rowset = 0 ;)
The trace in question is saving to files on disk, and doesn't clean up (or get cleaned up) after itself, so I have to manually delete files to keep the disk from filling up...
I don't (yet) want to stop the trace, until I know what it's doing...
Thanks,
Jason A.
March 13, 2013 at 7:41 am
I Tripped over this same issue myself, even wrote an article about it long ago(Reverse Engineering a Server Side Trace[/url])
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
March 13, 2013 at 7:46 am
March 13, 2013 at 7:53 am
And this particular query in your article:
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
gave me enough info to figure out what's going on, and match up what's happening with a script found stashed in a network share with no documentation and cryptic comments...
Thanks Lowell!
Jason A.
March 13, 2013 at 7:55 am
glad i helped a little bit Jason! i fixed the link in my original post also, thank you!
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply