SQL Server tracing system is used very often by DBAs and programmers for pinpointing issues in the databases. It’s supported by few system objects that describes itself including list of events, columns of each event might return, start and stop event, retrieve event data, status of each event sessions, etc. SQL Profiler utilizes those objects to retrieve and show the trace information on the screen. From debugging perspective, it’s enough for a SQL developer. DBAs, however, prefers to create trace programmatically and save the trace in the database or file system for alerting and later performance analysis. You can use SQL Profiler to trace the SQLs issued by anbother SQL Profiler to generate the trace procedure sequences. Alternatively, you can also browse trace related DMVs to find out all trace related metadata.

sys.trace_categories lists categories of the events. sys.trace_eventslists all the events can be tracked under each categories. Use following SQL get list of events and their categories

select a.name Category, b.name EventName, b.trace_event_id EventID
from sys.trace_categories  a
	inner join  sys.trace_events b on a.category_id = b.category_id
order by 1,2

Returning information for each event will be different. sys.trace_event_bindings tells you what columns will be returned from the event and sys.trace_columnsdefines column names, types, and other information used by SQL profiler.

select  a.name Category, b.trace_event_id EventID, b.name EventName, d.trace_column_id FieldID, d.name FieldName, d.type_name Type, d.max_size MaxSize
from sys.trace_categories  a
	inner join  sys.trace_events b on a.category_id = b.category_id
	inner join sys.trace_event_bindings c on c.trace_event_id = b.trace_event_id
	inner join sys.trace_columns d on d.trace_column_id = c.trace_column_id
order by a.name, b.name, d.trace_column_id

Some columns return integer numbers which are a lookup keys for values, such as EentSubClass, IntegerData2, ObjectType, etc. The names of the lookup keys are saved in sys.trace_subclass_values. For instance

select * from sys.trace_events where trace_event_id = 46
select * from sys.trace_event_bindings where trace_event_id = 46
select * from sys.trace_subclass_values where trace_column_id = 21 and trace_event_id = 46
select * from sys.trace_columns where trace_column_id = 21

The Trace Event 46 is Object:Create. Column EventSubClass can have 3 possible values 0, 1, and 2, which stand for Begin, Commit, and Rollback.

sys.traces table lists all the trace sessions running or sleeping in the system. It should be monitored occasionally. Any unreleased sessions should be removed.

select * from sys.traces

Function fn_trace_geteventinfo(traceid)list events and columns captured by session traceid. You can use following query to check what has been traced.

select  a.name Category, b.trace_event_id EventID, b.name EventName,d.trace_column_id FieldID, d.name FieldName, d.type_name Type, d.max_size MaxSize
from sys.trace_categories  a
	inner join  sys.trace_events b on a.category_id = b.category_id
	inner join sys.trace_event_bindings c on c.trace_event_id = b.trace_event_id
	inner join sys.trace_columns d on d.trace_column_id = c.trace_column_id
	inner join fn_trace_geteventinfo(1) e on e.eventid = b.trace_event_id and e.columnid = d.trace_column_id
order by a.name, b.name, d.trace_column_id

Filter information for existing trace sessions can be retrieved by function fn_trace_getfilterinfo(traceid).

select * from fn_trace_getfilterinfo(2);

That’s all about the metadata of the tracing system. In next post, I will tell you how to programmatically setup a trace and save the trace as files locally or stream to you application directly.