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
- In the original script the
WITH (STARTUP_STATE = ON)clause was placed after aGO, which splits it from theCREATE EVENT SESSIONstatement. It is now part of the same statement. - The event file name is generic (
error_log_demo.xel). - The cleanup command targets
D:\SqlTraceFiles, while the session writes to the SQL ServerLogfolder. Align the two paths before running the cleanup.