Get trace definition from a running trace

On one of the Production SQL Server 2005 instance we were getting “Access Violation” error. This error would trigger a huge .mdmp file to be generated. During this time, the instance would become unresponsive. Since this was occurring at least 5 times a week, we opened a case with Microsoft PSS.

On investigation, the engineer opined that the traces running on the instance while capturing OLEDB related events were triggering Access Violation error. Event IDs 61, 119, 120, 121 and 194 were the ones which were related to OLEDB. On our instance, we have 3 traces running almost every 30 minutes. These are part of various Audit\Application requirements. We needed to know which trace is capturing these events. Here are the scripts that I used to get that information.

SELECT distinct traceid 
FROM ::fn_trace_getinfo(default);

This query will list the traceid of the traces that are currently active on the instance.

trace.eventid ,
events.Name as EventName ,
trace.columnid ,
cols. name as ColumnName
from :: fn_trace_geteventinfo ( 2 ) AS trace 
INNER JOIN sys.trace_events events 
ON trace.eventid = events.trace_event_id
INNER JOIN sys.trace_columns cols 
ON trace.columnid = cols.trace_column_id

In this query I am fetching the trace definition of traceid 2. The output was something similar to the one below.

With this output it was easy out filter out the OLEDB events and it was the traceid 2 which was capturing those events. We will be implementing a Change Control to exclude these events from being traced. Hope this will resolve the issue permanently. Keeping my fingers crossed!

One thought on “Get trace definition from a running trace

Comments are closed.