T-SQL Tuesday 166: Extended Events

This month’s T-SQL Tuesday invite comes from Grant Fritchey. Grant points out that Extended Events is a rare topic when it comes to T-SQL Tuesday, having only been the topic on one previous occasion. It’s time to make some history and double that, as this month Grant asks us to write about Extended Events. Click the T-SQL Tuesday logo to check out the full invite.

Many times, when I’m using Extended Events, I’m filtering on an Id or procedure name in query text and tracking what’s hitting a database. In these cases, I’m assuming queries are going to complete successfully. Did you know Extended Events can be useful when you’re expecting things to go wrong? Let’s look at how we can use Extended Events to track down error messages.

Catching Errors with Extended Events

I created a basic Extended Events session to track the error_reported event.

Let’s start that session, filter by the StackOverflow2013 database, and generate some errors.

I have a GetUserById stored procedure to return a user from the database. Instead of searching by an Id, I mistakenly searched by a name:

Checking my Extended Events session, I’ll find a record of that error:

While leaving that session running, I’m going to cause more errors. I’ll update the Location for a user to be the longest city name in the world, which is the ceremonial version for Bangkok. Spoiler alert: the city name is much longer than 100 characters than the Location field is designed for:

To make matters worse, I’m going to ignore Albert Einstein’s definition of insanity and retry my UPDATE a couple more times. Now, let’s look at the Extended Events session:

Grouping Errors

From this view, I’m flooded with the most recent error happening again and again. I want a more concise view of what errors are occurring, so I’m going to stop the data feed, select Grouping, and group by error_number:

I’ll click the OK button, and now I have an easier view to read from:

In this case, we want to focus on the errors occurring the most so we can expand the top error 8152 which is our “String or binary data would be truncated” error:

And we can also check the 3621 error, which is a matching “The statement has been terminated” error:

We can highlight an error and check below for more information, such as what query caused the error:

All Together

If you’re tracking errors and need an easier way to group them together, Extended Events can help.

Thanks for reading!