March 8, 2013 at 1:09 pm
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
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
March 8, 2013 at 1:34 pm
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
March 8, 2013 at 1:38 pm
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
March 20, 2013 at 8:42 am
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 ?
March 20, 2013 at 9:51 am
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
March 20, 2013 at 11:24 am
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 ?
idstatuspathmax_size stop_time
21NULLNULLNULL
March 20, 2013 at 11:28 am
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 ?
idstatuspathmax_size stop_time
21NULLNULLNULL
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
March 20, 2013 at 11:43 am
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 :unsure:
Sorry I misread your end of post so the path is not needed !
Thanks a bunch !
March 20, 2013 at 12:01 pm
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 :unsure:
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
March 25, 2013 at 1:53 pm
Lowell (3/20/2013)
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 :unsure: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
Thanks for your input but i think i'm a bit confused now 🙁
When u say to use the server side trace instead of a command line profiler ( not GUI )
I just launch it this way
DECLARE @rc int, @TraceID int, @on BIT, @MaxSize bigint
SET @MaxSize = 50
-- EXEC @rc = sp_trace_create @TraceID output, 2, N'f:\sqldba\trace\SQL_AUDIT_xxx_PROD'
EXEC @rc = sp_trace_create @TraceID output, @options = 2, @tracefile = N'f:\sqldba\trace\SQL_AUDIT_xxx_PROD', @maxfilesize = @MaxSize
SELECT RC = @rc, TraceID = @TraceID
....
ECLARE @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
exec sp_trace_setevent 2, 15, 8, @on
exec sp_trace_setevent 2, 15, 9, @on
What is wrong with the profiler command uless I'm misunderstanding
""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 """
March 25, 2013 at 2:15 pm
ok you are doing a server side trace, so you are good to go;
when i read commandline, i had assumed a call to profiler.exe with some parameters to a trace definition.
you got it!
Lowell
March 26, 2013 at 7:41 am
I was just told to schedule a weekly stop and restart of this configured trace and therefore must put it in as an agent job.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply