What Events does the Default Trace Capture?

Question

What Events does the Default Trace Capture?

Leo.Miller

SSChampion

Points: 13048
More actions
July 21, 2010 at 5:41 pm

#221509

I've done a bit of work with the default trace, but I've not been able to find a list of what events are captured. For a user trace you can run SELECT * FROM ::fn_trace_geteventinfo(n), where n is the Trace ID. The default trace has an ID of 0, but when you run the above function it doesn't return any rows. So far I've just looked in the trace file to see if an event is being logged or not, but this doesn't help if the event hasn't occurred, and I don't want to generate a host of events just to see if they are being traced.
Does anyone have list of the default trace events, or know where I can find one?
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 1

[font="Courier New"]

In this post, I have listed the query as well as all the events traced in the default trace. Basically the default trace id is 1 NOT 0.

I don't have a SQL server instance to write the query now.

select distinct trace_event_id, e.name

from ::fn_trace_geteventinfo(1) t join sys.trace_events e

on t.event_id = e.trace_event_id

http://sankarreddy.com/2010/04/t-sql-tuesday-005-who-put-my-database-offline/[/url][/font]

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 2

Just a Correction in the above query.

select distinct trace_event_id, e.name

from ::fn_trace_geteventinfo(1) t join sys.trace_events e

on t.eventid = e.trace_event_id

t.event_id is actually t.eventid

Lowell SSC Guru Points: 323504 More actions · Answer 3

here's the default trace scripted out so you can see and add more events, etc and create a replacement that might last longer than the default:

default_trace_script.txt

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!

sivaprasad SSCertifiable Points: 6183 More actions · Answer 4

Refer the link below for the events on the Default trace.

http://blogs.technet.com/b/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

sivaprasad SSCertifiable Points: 6183 More actions · Answer 5

the query below may help you;

SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description

FROM ::fn_trace_geteventinfo(1) Trc

JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

cbielins Grasshopper Points: 20 More actions · Answer 6

Is there a way to capture all events for all traces with an SQL query? I understand that you can use the fn_trace_geteventinfo(n) to retrieve information on a particular trace, but what if you want to capture all events and you don't know how many active traces there are?

Lowell SSC Guru Points: 323504 More actions · Answer 7

they way i would recommend is by using the script i contributed in this article:

Reverse Engineer A Database Trace[/url]

first find all the traces on your server with SELECT * FROM sys.traces

then for each of the traces on that list, script it out...

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!

cbielins Grasshopper Points: 20 More actions · Answer 8

hey Lowell...

Yea, I saw that script. I have been playing with that, but I finding that there is a disconnect with the trace ids and the trace events within the system views. It looks like the only way to associate the two is first figuring out which traces are out there, and run your script.

I'm looking for a way to satisfy some compliance benchmarks automatically on a high level, without firsthand knowing how many and which traces are out there. So basically, logging on to an instance, and running premade SQL looking for output without having to do some research.