Systems Engineering and RDBMS

Event Classes in SQL Server 2005

Posted by decipherinfosys on February 7, 2007

This is so convenient.  In SQL 2000, one had to keep a static list of the event classes and then reference them for look-ups when running diagnostic SQL statements against the data captured in SQL Server Profiler traces.  In SQL Server 2005, there is a new view that gives you this information : sys.trace_events.  You can use this and cross reference it with the trace data that you store in a table, example (trace_take_two is the trace table in which we saved our data):

select v1.trace_event_id, as event_name, as category_name, x.cnt as nbr_of_occurences
sys.trace_events as v1
inner join (select eventclass, count(1) cnt from trace_take_two group by eventclass) as x
on x.eventclass = v1.trace_event_id
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
order by v1.trace_event_id asc

There are other trace related views as well that you can use to build up a quick troubleshooting set of scripts.

Sorry, the comment form is closed at this time.

%d bloggers like this: