Extended Events -capture query errors

02 March,2020 by Jack Vamvas

If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors.

To use the script you'll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files.

I usually place them in the C:\tmp - although be careful just in case you fill the C:\ drive! The alternative is to use another drive.

CREATE EVENT SESSION [CaptureErrors] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username,
sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([severity]>=(10)))
ADD TARGET package0.event_file(SET filename=N'C:\tmp\CaptureErrors.xel',metadatafile=N'C:\tmp\CaptureErrors.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [CaptureErrors] ON SERVER 
	STATE = START;
GO

SELECT
    [XML Data],
    [XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME')             AS [Timestamp],
    [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)')        AS [Database],
    [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')                AS [Message],
    [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [Statement]
FROM
    (SELECT
        OBJECT_NAME              AS [Event], 
        CONVERT(XML, event_data) AS [XML Data]
    FROM
        sys.fn_xe_file_target_read_file
    ('C:\tmp\CaptureErrors.xel',NULL,NULL,NULL)) as FailedQueries;
GO

ALTER EVENT SESSION [CaptureErrors] ON SERVER 
	STATE = STOP;

Read More on Extended Events 

How to Audit user DML with SQL Server Extended Events (SQL ...

How to read and parse the Microsoft SQL Server extended event log ...

Troubleshoot SQL Security Error Ring Buffer Recorded - System ...

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Extended Events -capture query errors


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer