Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

Map SQL Server Profiler EventClass ID to its name in a saved trace table

If you've ever worked with SQL Server profiler then you're familiar with the EventClass column in the trace.

It tells you what event is being monitored for each row. When you save this trace to a table you can see

that the EventClass no longer has a description but an ID. So how do you map this to a description?

If you know where to look it's amazingly simple but if you don't here's a hint: sys.trace_events

 

Here's a script to help you out:

SELECT   TE.name, T.*
FROM     dbo.Trace T – table that contains the trace results
         JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
ORDER BY RowNumber

 

kick it on DotNetKicks.com

Legacy Comments


Kevin Bailey
2008-01-29
re: Map SQL Server Profiler EventClass ID to its name in a saved trace table
I was also looking for EventSubclass; here it is. This is for analyzing server-side traces, which are only saved to files. You could SELECT INTO a table.

-- Where do Event Class and Event Subclass get populated from?
SELECT e.name EventClassName, v.subclass_name, t.*
FROM ::fn_trace_gettable('your_trace_filepath.trc', default) t
JOIN sys.trace_events e ON e.trace_event_id = t.EventClass
JOIN sys.trace_subclass_values v ON v.trace_event_id = e.trace_event_id AND v.subclass_value = t.EventSubClass

Imran
2008-03-28
re: Map SQL Server Profiler EventClass ID to its name in a saved trace table
Thanks, This was helpful.. I wasted nearly one hour, just searching from where the table table getting that id... where is the parent table... Microsoft guys used the name of the columns as they wanted... there is no SYNC. some where they use as one name and at other place they call the same column, some other name.

Anyways thanks .. this was really helpful... keep posting good stuff.


Thanks,
Imran.

Barb Wendling
2008-11-28
re: Map SQL Server Profiler EventClass ID to its name in a saved trace table
Much better help than BOL - Thanks!

Eric
2009-03-18
re: Map SQL Server Profiler EventClass ID to its name in a saved trace table
Can't get this to work in 2000, though, so don't try. :)

Still looking for the other way in 2000