October 11, 2011 at 7:15 am
Executing this command on SQL Server 2005 Enterprise SP2 64 bit:
select * from fn_trace_getinfo(default)
returns these results:
traceid property value
1 1 2
1 2 S:\SQLDB\MSSQL.1\MSSQL\LOG\log_1340.trc
1 3 20
1 4 NULL
1 5 1
2 1 1
2 2 NULL
2 3 NULL
2 4 NULL
2 5 1
3 1 1
3 2 NULL
3 3 NULL
3 4 NULL
3 5 1
I know the first trace is the default trace but what are the other 2? Thanks for any help.
October 11, 2011 at 7:31 am
Lee you've got at least one custom trace, possibly two...
i built this proc and contributed an article about it to help Reverse Engineer a Server Side Trace.
this creates a procedure, which you can then call like this for known traceID's.
The advantage is it decodes/identifies all the events being tracked, as well as the fitlers applied...makes it very easy to review a trace:
you call it for any known traceId:
EXEC sp_ScriptAnyTrace 1;
EXEC sp_ScriptAnyTrace 2;
EXEC sp_ScriptAnyTrace 3;
Lowell
October 11, 2011 at 7:32 am
Have you tried fn_trace_geteventinfo to see what they're capturing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2011 at 7:58 am
Wow! Thanks Lowell. Excellent procedure. I have no idea where the trace originated from but it looks like it is for deadlocks. Here is the output from your excellent proc, thanks again:
ResultsId ResultsText
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 --declare variables for parameterizing the command
2 declare @traceidout int
3 declare @options int
4 declare @path nvarchar(256)
5 declare @maxfilesize bigint
6 declare @maxRolloverFiles int
7 declare @stoptime datetime
8 declare @on bit
9
10 set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.
11 set @maxfilesize = 20 --size in MB
12 set @maxRolloverFiles = 5 --number of files; ie if 5 files, start rewriting on rollover
13 set @stoptime = NULL -- null if never ends, else a specific date
14 set @options = 0 -- TRACE_FILE_ROLLOVER = FALSE, SHUTDOWN_ON_ERROR = FALSE
15 set @path = 'mytrace' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting
16
17 --create the trace
18 exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles
19
20 --for the Event Every SQL statement completed, capture columns of accessible data
21 exec sp_trace_setevent @traceidout,148,1,@on --Deadlock graph,TextData
22 exec sp_trace_setevent @traceidout,148,12,@on --Deadlock graph,SPID
23 exec sp_trace_setevent @traceidout,148,14,@on --Deadlock graph,StartTime
24 exec sp_trace_setevent @traceidout,148,51,@on --Deadlock graph,EventSequence
25 --filters
26
27 -- WHERE 1 = 1
28 ---final step
29
30 --turn on the trace
31 exec sp_trace_setstatus @traceidout, 1 ---start trace
32 --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
33 --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
October 11, 2011 at 8:12 am
Thanks for the quick response. It looks like they are tracing deadlock graph, event id 148. No tsure why they are there since trace flag 1222 is not enabled. But I feel better knowing what they are. Thanks again.
October 11, 2011 at 11:00 am
Mystery Solved!
The odd traces are being created by our monitoring software, Foglight for SQL Server (Quest Software).
Thanks again for everyone's help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply