Viewing errors with extended events

1

September 27, 2022 by Kenneth Fisher

A little while back I came across this post: Identifying failed queries with extended events. It has a script to create an extended event that tracks failed queries. I cannot tell you how excited I was by this. Now, I can hear some of you thinking Well, I mean that’s interesting and all but why the big deal? I go through some variation of the following conversation at least once a week.

Dev: We are getting an error: SELECT permission was denied on the object MyObject.
Me: Ok, what instance, database, and account is getting the error?
Dev: The instance is InstanceA and we think the database is DatabaseA but we have no idea what the service account is.

Now, watch this.

-- Source: https://www.sqlservercentral.com/blogs/identifying-failed-queries-with-extended-events
--- Create Xevent
------ Be sure to change the filename and metadatafile
CREATE EVENT SESSION [FailedQueries] ON SERVER 
ADD EVENT sqlserver.error_reported 
	(ACTION(sqlserver.client_app_name, sqlserver.client_hostname,  
		sqlserver.database_name, sqlserver.sql_text, sqlserver.username
		--, sqlserver.tsql_stack /*include this if needed*/
		) 
	WHERE ([package0].[greater_than_int64]([severity], (10)))) 
ADD TARGET package0.event_file (SET 
	filename = N'E:\Sql_EE_FailedQueries.xel'
	,metadatafile = N'E:\Sql_EE_FailedQueries.xem'
	,max_file_size = (5)
	,max_rollover_files = (10))
WITH (STARTUP_STATE = OFF) -- Change to ON if you want this to start when SQL starts.
GO
--- Start Xevent
ALTER EVENT SESSION [FailedQueries] ON SERVER 
STATE = START;
GO

Now I open up a window to watch live data and have the user attempt to run their code:

And now I have confirmed that the database name is Kenneth_Test, the user is UserA and I even have the exact command they ran that is giving them an error. More than enough to solve the problem.

Of course there are a lot of other possible problems you might use this for, and on a stable production system I might even consider setting this up with an alert to send me any errors that are occurring. That way I can easily see if some new code has been installed that is having a problem, or even possibly if someone is trying to work out how to do something malicious.

I’ve got some additional pieces of code related to this on my GitHub. A script to turn off the session and another one to read information that’s already in the file the session is writing to. And of course you can get most if not all of this from the original poster (link above).

One thought on “Viewing errors with extended events

  1. […] Kenneth Fisher performs some diagnostics: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,739 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: