How to capture query errors using SQL Server Extended Events

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 ...