How to get name when given event class from trace

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to get name when given event class from trace

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-26 : 12:25:54

When viewing trace information via SQL Profiler, you can see the name of the event class. But when viewing trace information via T-SQL (either from a trace table or trace file using ::fn_trace_gettable), you see the id of the event class and not its name.

If I knew the name of the event class, then I could easily find the id by drilling down from this BOL article:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm

For instance, I recently captured Lock:Acquired event class from the Locks event category. I can see from BOL that its id is 24 by drilling down into Locks:Acquired Event Class from the above link. I just have to view the Description field for EventClass data column.

Is there a way to do this in reverse, meaning if I knew the id of the event class, could I find the name easily? Is SQL Profiler using a built-in function to convert the id to a name?

I've searched through BOL for this information, but haven't been able to locate an article that lists all of the event classes by id and their associated names. I've also scoured Google for this information and haven't been able to locate it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-10-27 : 00:40:05

Hey Tara, try this

use master;
GO
select * from sys.trace_events;

and no, they sure don't make it easy to find

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-27 : 00:43:23

quote:
Originally posted by russell

Hey Tara, see if this helps

use master;
GO
select * from sys.trace_events;

Exactly what I was looking for!

I had found the ids to event name mapping in an XML file (any machine with SQL Profiler would have it), but that was going to take some work to get it queryable.

Thank you russell!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-10-27 : 00:45:49

My pleasure to help the Almighty Goddess of SQL Team

by the way, i really struggled with the exact same issue a while back. almost made my own table by clicking through the exact BOL page you posted b4 i found that table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources