TraceErrors Process

TraceErrorsReport
In the TraceErrorsReport stored procedure I use sqlsig, a CLR user-defined function (UDF) created by Itzik Ben-Gan. With help from Ray Maor of DBSophic, I modified sqlsig to remove some unwanted characters, such as CR and LF (ASCII 13 and 10), that caused problems rendering the tabular result set into the HTML report used by the calling application.

The UDF works on the TextData column of the rows generated by the RPC:Starting and SQL:BatchStarting events. It replaces specific characters, such as parameters, with a hash sign, which lets it aggregate the data, preventing the same error from showing up too many times in the report.

The @Rows input parameter passed to the TOP clause of the SELECT statement allows you to control the number of rows returned. Note that the returned result set isn't sorted in any order. My first choice would be to sort the results by the StartTime column, but to do so I would have to include the StartTime column in the SELECT list. Including StartTime would make the DISTINCT operator totally worthless, because a SELECT statement that uses the DISTINCT operator requires the columns in the ORDER BY clause to be part of the SELECT list.

I didn't include important columns such as StartTime, EventSequence, and SPID for the sake of aggregation. However, this data is captured and available at in the TraceErrorsFull table, where it remains until the next time the procedure runs, so the data is available to you while you're investigating a specific case.

The MIN_StartTime and MAX_StartTime columns come from TraceErrorsFull, which holds the raw trace data. I included them in the report only so that you know the timeframe of events that the report worked on. You can omit these columns or modify the procedure to return an additional result set with just these two columns. The Hours column shows the difference in hours between these two columns.

TraceErrorsStatus
The TraceErrorsStatus stored procedure is aimed at making sure the trace is running and active. The procedure is invoked by a job that has two schedules. The first schedule executes the stored procedure when the SQL Server Agent service is started, which results in the trace being started. The second schedule runs every hour to make sure that the trace exists and is in an active state. Alternately, the procedure can be invoked by a startup stored procedure instead of the first schedule. This eliminates the SQL Server Agent service as a point of failure and starts the trace a little earlier.

Once your trace is created, you can view its state and additional information by querying the system views. Use the query in Listing 1 as a starting point.

-- Get all traces
SELECT
	 [id], , event_count, dropped_event_count, [path]
	,max_size, max_files, is_rollover, is_shutdown, buffer_count
	,buffer_size, file_position, start_time, last_event_time
FROM sys.traces;


-- Get Events
SELECT
	 e.name AS [event]
	,c.name AS [column]
FROM fn_trace_geteventinfo(2) ei --< edit TraceId
INNER JOIN sys.trace_events e ON ei.eventid = e.trace_event_id
INNER JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id;


-- Get Filters
SELECT
	 columnid
	,c.name AS [column]
	,logical_operator
	,comparison_operator
	,\[value]
FROM fn_trace_getfilterinfo(2) ei  --< edit TraceId
INNER JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id;


-- Get SubClass values
SELECT
	 c.name AS [column]
	,e.name AS [event]
	,s.subclass_value
	,s.subclass_name
FROM sys.trace_columns c
INNER JOIN sys.trace_subclass_values s ON c.trace_column_id = s.trace_column_id
INNER JOIN sys.trace_events e ON e.trace_event_id = s.trace_event_id
WHERE e.name LIKE 'Audit Login';


-- Get Events / Columns combination
SELECT
	 e.name AS [event]
	,c.name AS [column]
FROM sys.trace_event_bindings b
INNER JOIN sys.trace_events e ON e.trace_event_id = b.trace_event_id
INNER JOIN sys.trace_columns c ON c.trace_column_id = b.trace_column_id
WHERE e.name LIKE 'Audit Login'
ORDER BY e.name;

Why Capture?
Capturing SQL exceptions at the very lowest level, the database engine, is an important aspect of monitoring production systems. Capturing the exceptions provides you with your own set of logs so that you don't need to rely only on logs generated by applications, which can sometimes be unreliable or, in some environments, inaccessible to DBAs. Capturing RPC:Starting and SQL:BatchStarting events is truly a necessity on production systems, where a DBA needs to find out quickly what happened when things go wrong. Knowing what happened is an important step in preventing the same problem from being repeated.

• SQL Server Log Files
• Automate SQL Server Error Log Checking
• When SQL Server Tool Vendors Compete, DBAs Win
• Snare Those Errors

• Introducing SQL Server Profiler
• SQL Server 2005 Profiler reference
• Using SQL Server Profiler
• sp_trace_setevent – contains a table with all events and columns