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.
select 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!