A Lightweight Error Audit with Extended Events
The application team swears the error happened. The SQL Server error log, when you finally open it, is a wall of successful-backup messages and “Changed database context to” lines, and somewhere in there, maybe, is the one real error that matters. By the time you find it the log has rolled over anyway. The error log is a firehose of mostly-informational text with no structure, no login attribution, and no easy way to ask “show me every severity 16+ error in the SalesDB in the last four hours.”
The error_reported event answers exactly that question, and Extended Events lets you decide what counts as an error before you ever store a row. This is the third post in the Extended Events for the working DBA series. It uses the same capture-and-persist pipeline as the deadlock post and the blocked process post, but the lesson this time is filtering: the value of an error session lives almost entirely in the predicate that decides what to keep.
Why the Error Log Is the Wrong Tool
The SQL Server error log mixes three things that have nothing to do with each other: genuine errors, routine operational messages (backups, log truncation, context changes), and informational chatter from statistics and full-text search. It is a flat text file, so answering anything quantitative means parsing strings. It rolls over on restart and on a schedule, so history is short. And it records the message but not, in any queryable form, who ran the statement that produced it.
error_reported fires for every error SQL Server raises, and through Extended Events actions you can attach the login, the host, the application, the database, and the offending SQL text to each one. The result is a table you can filter, group, and join, instead of a log you have to grep.
The Predicate Is the Point
error_reported is a high-frequency event. Left unfiltered it captures every backup-completed message, every “DBCC printed” line, and every informational note, and your table fills with noise. The fix is a WHERE clause on the session itself, so SQL Server discards the noise at the source and never pays to serialise it. This is the heart of the session: a list of error numbers that are informational rather than actionable, plus an exclusion of msdb (where Agent housekeeping raises errors you can do nothing about).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
USE [master]; GO IF EXISTS ( SELECT 1 FROM [sys].[server_event_sessions] AS [ss] WHERE [ss].[name] = N'errors' ) BEGIN ALTER EVENT SESSION [errors] ON SERVER STATE = STOP; DROP EVENT SESSION [errors] ON SERVER; END; CREATE EVENT SESSION [errors] ON SERVER ADD EVENT [sqlserver].[error_reported] ( ACTION ( [package0].[last_error] , [sqlserver].[server_principal_name] , [sqlserver].[client_app_name] , [sqlserver].[client_hostname] , [sqlserver].[database_name] , [sqlserver].[sql_text] ) WHERE ( /* Drop the informational and operational noise at the source. */ [error_number] <> 3014 /* Backup processed N pages ... */ AND [error_number] <> 3211 /* N percent processed. */ AND [error_number] <> 3262 /* The backup set on file N is valid. */ AND [error_number] <> 5701 /* Changed database context to '...'. */ AND [error_number] <> 5703 /* Changed language setting to ... */ AND [error_number] <> 9927 /* Full-text noise word(s). */ AND [error_number] <> 14570 /* Job outcome (Agent). */ AND [error_number] <> 17177 /* Process ID informational message. */ AND [error_number] <> 18264 /* Database backed up. Database: ... */ AND [error_number] <> 18265 /* Log was backed up. Database: ... */ /* msdb raises Agent housekeeping errors we cannot act on. master, model, and tempdb are deliberately NOT excluded. */ AND [database_name] <> N'msdb' ) ) ADD TARGET [package0].[ring_buffer] ( SET max_memory = 2048 , occurrence_number = 0 , max_events_limit = 0 ) WITH ( MAX_MEMORY = 10 MB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 15 SECONDS , STARTUP_STATE = ON ); GO ALTER EVENT SESSION [errors] ON SERVER STATE = START; |
The list above is trimmed for readability; a fuller exclusion set covers the backup, statistics, and “informational message only” numbers you will recognise from your own error log. Build the list to match your environment: run unfiltered for an hour, see what floods in, and add the noise numbers to the predicate. Two deliberate choices are worth calling out. First, msdb is excluded but master, model, and tempdb are not, because problems in those system databases are worth investigating. Second, filtering by error number is more precise than filtering by severity, since some severity 10 informational messages still matter and some higher-severity errors are benign in context.[1]
Persisting and Scheduling
The harvest procedure and the Agent job are the same pattern as the previous two posts: drain the ring buffer into dbo.errors_xml_events in the DBA database, dedup on timestamp, then stop and restart the session to empty the buffer. I will not repeat the full procedure here; take GatherDeadlockEvents from the first post, rename it to GatherErrorEvents, point it at the errors session, and write to dbo.errors_xml_events. Schedule it every five minutes exactly as before. The ring buffer caveats are unchanged.[2]
Triage by Severity and Login
Once the errors are in a table, the analysis procedure shreds the XML into columns you can actually triage on: the message, the error number, the severity, the state, the database, and crucially the login and application that triggered it. The SQL text is captured too but gated behind a parameter, because it can be large and occasionally sensitive.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
USE [DBA]; GO CREATE OR ALTER PROCEDURE [dbo].[AnalyzeErrorEvents] ( @show_statement_text bit = 0 , @maximum_hours_back int = 4 , @timezone sysname = N'Central Standard Time' ) AS BEGIN SET NOCOUNT ON; DECLARE @StartTime datetimeoffset(3) = DATEADD(HOUR, -@maximum_hours_back, SYSUTCDATETIME()); DECLARE @EndTime datetimeoffset(3) = SYSUTCDATETIME(); SELECT [message] = [x].[xeXML].value('(/event/data[@name="message"]/value)[1]', 'nvarchar(512)') , [error_number] = [x].[xeXML].value('(/event/data[@name="error_number"]/value)[1]', 'int') , [severity] = [x].[xeXML].value('(/event/data[@name="severity"]/value)[1]', 'int') , [state] = [x].[xeXML].value('(/event/data[@name="state"]/value)[1]', 'int') , [database_name] = [x].[xeXML].value('(/event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') , [client_hostname] = [x].[xeXML].value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') , [client_app_name] = [x].[xeXML].value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') , [server_principal_name] = [x].[xeXML].value('(/event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(128)') , [EventTimeLocal] = [x].[xeTimeStamp] AT TIME ZONE @timezone , [sql_text] = CASE WHEN @show_statement_text = 1 THEN [x].[xeXML].value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') ELSE NULL END FROM [dbo].[errors_xml_events] AS [x] WHERE [x].[xeXML].exist('/event[@name="error_reported"]') = 1 AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1 AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1 ORDER BY [x].[xeTimeStamp]; END; GO |
The severity column is the triage key.[3] Severities 11 through 16 are the everyday application errors, constraint violations, conversion failures, missing objects, the things a developer can fix. Severities 17 through 19 mean SQL Server itself hit a resource or internal limit, and 20 and above signal a problem serious enough that the connection was usually closed. Sorting and grouping by severity turns a flat list into a worklist: deal with the 20s first, then see which 16s are recurring.
What the Audit Trail Buys You
Because every row carries the login, the host, and the application, the same table answers questions the error log cannot. Group by server_principal_name and you can see that one service account is generating a thousand conversion errors an hour, a smell of a broken deployment. Group by error_number and a single recurring 2627 (primary key violation) points at a retry loop hammering a duplicate insert. Filter to severity 20+ and you have a clean list of the serious incidents to put in the morning report.
|
1 2 3 4 5 6 |
error_number severity count server_principal_name database_name ------------ -------- ----- ----------------------- ------------- 8152 16 1,204 svc_etl_loader StagingDB 245 16 318 app_reporting SalesDB 2627 14 201 app_orders SalesDB 9002 17 2 svc_etl_loader StagingDB |
That first row, error 8152 (string or binary data would be truncated) over a thousand times from the ETL loader, is a single root cause masquerading as a flood. The 9002 (transaction log full) twice at severity 17 is a different and more urgent kind of problem. Neither is easy to spot in a text log; both are obvious once the errors are rows in a table you can GROUP BY.
Caveats
- Tune the predicate to your instance. The exclusion list shown is a starting point. Run the session unfiltered briefly, identify the informational numbers that dominate, and add them. An over-broad filter is as bad as no filter.
- Gate the SQL text. The captured statement text can contain literal values, including data you would rather not store unencrypted. The procedure keeps it off by default for good reason; turn it on deliberately and mind retention.
- Severity is a guide, not a verdict. A severity 16 that repeats thousands of times can matter more than a one-off 17. Use frequency alongside severity.
- The ring buffer and restart caveats carry over. Same small-and-frequent harvest, same loss of buffered events on a service restart, as in the first post.
- Add retention. Errors accumulate faster than deadlocks; prune the table to your review window.
The Takeaway
An error session is only as good as its predicate. Filter the operational and informational noise out at the source, keep the genuine errors with their login and application attached, and the error log stops being a grep target and becomes a queryable audit trail. Triage by severity, group by login or error number, and the recurring root cause that was hiding inside a thousand log lines shows up as the top row of a result set.
How do you keep track of errors across your instances, the error log, a custom session like this, or a third-party tool? I would like to hear what works for you. Find me on Bluesky or LinkedIn.
More in this series
- Part 1: From deadlock graph to repeat offender
- Part 2: Blocked process reports, from capture to root cause
- Part 3: A lightweight error audit with Extended Events (this post)
- Part 4: Mining the system_health session you already have
- Part 5: Catching slow file I/O with a filtered session
References
- Extended Events and the error_reported event – Microsoft Learn. Capturing errors with Extended Events and the actions available on the event. ↩
- Why I hate the ring_buffer target in Extended Events – Jonathan Kehayias, SQLskills. The truncation behaviour behind the small-and-frequent harvest design. ↩
- Database engine error severities – Microsoft Learn. What each severity level means and which ones close the connection. ↩