Monitoring Exception Events with Traces

This is Part 5 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In Part 2, he examined types of exceptions. In Part 3, Adam broke down the parts of the dreaded error message. In Part 4, he took a steely-eyed look at the RAISERROR function. Here, he offers a quick summary of how to use the Profiler trace to monitor for exceptions.

Some application developers go too far in handling exceptions, and end up creating applications that hide problems by catching every exception that occurs and not reporting it. In such cases it can be extremely difficult to debug issues without knowing whether an exception is being thrown. Should you find yourself in this situation, you can use a Profiler trace to monitor for exceptions occurring in SQL Server.

In order to monitor for exceptions, start a trace and select the Exception and User Error Message events. For most exceptions with a severity greater than 10, both events will fire. The Exception event will contain all of the data associated with the exception except for the actual message. This includes the error number, severity, state, and line number. The User Error Message event will contain the formatted error message as it was sent to the client.

For warnings (messages with a severity of less than 11), only the User Error Message event will fire. You may also notice error 208 exceptions (“Object not found”) without corresponding error message events. These exceptions are used internally by the SQL Server query optimizer during the scope-resolution phase of compilation and can be safely ignored.

Note that in SQL Server 2008 and beyond, SQL Trace has a new partner tracing infrastructure, called Extended Events. One of the nice things that Extended Events brings with it is a unified error reporting event that includes the content of both the Exception and User Error Message events from SQL Trace. The Extended Event, called sqlserver.error_reported, is described in more detail, along with an example, in the following blog post from the team that created the feature:

Introduction to Extended Events