Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Validating trace file events Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 1:09 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
Hello,

If I have a trace running and populating some *trc files..
My question is how can I validate that i am indeed tracing the correct
sp_trace_setevent filters I have set ?

Are there dynamic tables where I can fetch the information on what is being traced?

Thank you !
JR
Post #1428744
Posted Friday, March 8, 2013 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 12,953, Visits: 32,481
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1428752
Posted Friday, March 8, 2013 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 12,953, Visits: 32,481
if i read youre request wrong, and you wanted to review the results of your trace, i use this:

--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 3 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1428756
Posted Friday, March 8, 2013 1:38 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
Awesome... thx for your help.

Indeed i just wanted to get an output and make sure that the SETEVENTS that were set when the trace was created are in fact being filtered.

I.e
DECLARE @on BIT
SELECT @on = 1
exec sp_trace_setevent 2, 14, 6, @on
exec sp_trace_setevent 2, 14, 7, @on
exec sp_trace_setevent 2, 14, 8, @on
exec sp_trace_setevent 2, 14, 9, @on
exec sp_trace_setevent 2, 14, 10, @on
exec sp_trace_setevent 2, 15, 6, @on
exec sp_trace_setevent 2, 15, 7, @on
Post #1428760
Posted Wednesday, March 20, 2013 8:42 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
Just to clarify ... if I am starting a custom trace ( not server default ) from profiler and would like to confirm the events that I am tracing .

Would these monitored events be in a sys table somewhere for me to query without having to go in the trace file ?
Post #1433261
Posted Wednesday, March 20, 2013 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 12,953, Visits: 32,481
johnnyrmtl (3/20/2013)
Just to clarify ... if I am starting a custom trace ( not server default ) from profiler and would like to confirm the events that I am tracing .

Would these monitored events be in a sys table somewhere for me to query without having to go in the trace file ?


technically it's a sys table valued function, but yes it's accessible;
taking a piece of the script i posted for scripting any trace, once you KNOW the trace you want to review, it's like this to see exactly which events, plus which columns:


--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 2 --this is the trace you want to review!
SELECT ISNULL(E.Name, '') As EventName
FROM ::fn_trace_geteventinfo(@TraceIDToReview) AS X
INNER JOIN sys.trace_events E
ON X.eventid = E.trace_event_id
GROUP BY E.Name


SELECT
ISNULL(E.Name, '') As EventName,
ISNULL(V.name, '') As ColumnName
FROM ::fn_trace_geteventinfo(@TraceIDToReview) 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



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433320
Posted Wednesday, March 20, 2013 11:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
K thanks !

I've launched a profiler trace and piped it towards a directory and a file...

I see 2 ID's within sys.traces but with the one I launched has a path of NULL ?
id status path max_size stop_time
2 1 NULL NULL NULL
Post #1433361
Posted Wednesday, March 20, 2013 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 12,953, Visits: 32,481
johnnyrmtl (3/20/2013)
K thanks !

I've launched a profiler trace and piped it towards a directory and a file...

I see 2 ID's within sys.traces but with the one I launched has a path of NULL ?
id status path max_size stop_time
2 1 NULL NULL NULL


typically the default trace is id=1; if you have C2 level auditing running then the C2 trace =1 and the default trace moves to = 2.

as for your quesiton, that is correct. profiler does not create a server side trace with a file/path ...Profiler traces have a NULL path.

I assume the actual data contained in the trace is in a temp table some place until profiler is closed.all in memory until profiler closes it's session. still accessible with the trace functions, but no path.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433364
Posted Wednesday, March 20, 2013 11:43 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
Since the trace will never be closed and it will be started via command line , how would I go upon this if at all possible in validating which events are being logged

Sorry I misread your end of post so the path is not needed !

Thanks a bunch !
Post #1433375
Posted Wednesday, March 20, 2013 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 12,953, Visits: 32,481
johnnyrmtl (3/20/2013)
Since the trace will never be closed and it will be started via command line , how would I go upon this if at all possible in validating which events are being logged


what? what do you mean validating which events? if you create a trace, it has the events you defined; nothing more and nothing less.

The scripts i provided allow you to spot check and see the events you specified, so you can be sure of yourself, but it will not auto-magically change, so what is there to validate?

Do you think SQL will somehow ignore the commands in your script, and suddenly not track the events you specified?

just a reminder, when you say "started via command line, you know better than to use Profiler 24/7, right? that's a major no no,and strongly frowned upon.

instead you create the trace server side, and let that run forever, if needed. then you query it as needed to review the data captured.

* I was thinking maybe by "validating which events", you really mean how do i see the traces contents.
i usually use something like this for a SERVER SIDE (not a profiler trace):


--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433386
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse