Here is a simple view that can help decipher the traces running on a SQL Server. It is especially handy if you inherit an existing server, but can also be useful to review your own traces as needed. When used in combination with SELECT * FROM sys.traces; it provides a rather robust overview of server trace activity.

-- Return column-level information on non-default traces
    N'TraceID' = t.id
    , N'CategoryID' = tCat.category_id
    , N'Category' = tCat.name
    , N'EventID' = tInfo.eventid
    , N'Event' = tEvent.name
    , N'ColumnID' = tInfo.columnid
    , N'Column' = tCol.name
    sys.traces AS t
    CROSS APPLY fn_trace_geteventinfo(t.id) AS tInfo
    INNER JOIN sys.trace_events AS tEvent
        ON tInfo.eventid = tEvent.trace_event_id
    INNER JOIN sys.trace_categories AS tCat
        ON tEvent.category_id = tCat.category_id
    INNER JOIN sys.trace_columns AS tCol
        ON tInfo.columnid = tCol.trace_column_id
    t.id <> 1 -- exclude the default trace

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: