LLI Wiki — My Digital Knowledge Base

Capture SQL Server errors with an Extended Events session

Create an Extended Events session that records SQL Server errors above severity 10 to an event file, and purge old .xel files.

An Extended Events session can record every error the engine raises, together with the client and statement context, without relying on the error log. The session below captures the error_reported event for errors with a severity above 10 and writes them to an event file.

Create the session

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)
    WHERE ([package0].[greater_than_int64]([severity], (10))))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\error_log_demo.xel')
WITH (STARTUP_STATE = ON);
GO

Remove old event files

The following PowerShell command deletes the .xel files older than 30 days in a folder.

Get-ChildItem -Path "D:\SqlTraceFiles" -Filter "*.xel" | where {$_.lastwritetime -lt (get-date).adddays(-30)} | Remove-Item -force

Notes

Mastodon LLIMastodon SocialFosstodon