How a simple Extended Events session can turn you from a silent guardian to a developer’s best ally.

There is an old, undeniable paradox in the life of a Database Administrator: If you do your job perfectly, nobody remembers you exist, only when you resolve problems. When queries are fast, backups are pristine, and the instance is highly available, the DBA remains in the shadows. We are usually only noticed when something goes terribly wrong. It’s a thankless dynamic, but it's the reality of infrastructure management.

However, there is a powerful way to step out of the shadows and add immense, visible value to your organization: by proactively catching the application errors that your development team can't see. Even in organizations with mature development teams, not every application error reaches the application logs. Some exceptions are swallowed, some are silently retried, and others simply disappear without leaving useful evidence. SQL Server, however, still knows exactly what happened once the request reaches the database engine.

The Developer's Blind Spot

In a perfect world, every application has flawless try/catch blocks, comprehensive logging mechanisms, and Application Performance Monitoring (APM) tools that capture every exception. The truth is that we often face a very different landscape:

• Black-Box Legacy Apps: Developers are tasked with maintaining applications written by someone who left the company several years ago, leaving behind zero documentation and swallowed exceptions.
• Lack of Tooling: Not every project has the budget for high-end APM.
• The Concurrency "Heisenbugs": Some errors simply do not exist in local development or QA environments. They only show their faces in production when hundreds of users hit the database simultaneously, causing unexpected deadlocks, resource exhaustion, or arithmetic overflows.

When an application silently fails, developers are left guessing. I’ve seen developers who don’t know where to look when trying to catch a bug. Their applications don’t have enough logging for it, but they know there is a bug because applications sometimes crash, sometimes take longer due to retries, and sometimes freeze.

But the SQL Server engine is the ultimate source of truth—it sees everything that hits the database.

The DBA to the Rescue: The Safety Net

By leveraging Extended Events (XEvents), we can configure a lightweight, highly efficient trap to catch every single error thrown by the SQL Server engine. Unlike many engine-level errors, these application-generated SQL errors typically do not appear in the SQL Server Error Log. Even when they do, the log lacks the SQL text, client application, and other context needed to troubleshoot the issue efficiently.

I’ve tested it on a SQL Server 2022 instance handling approximately 3,500 batches/sec, where I observed no measurable impact on CPU utilization after enabling the session. This is because the error_reported event is relatively inexpensive and only fires when SQL Server generates an error.

To make this actionable, we need to apply two crucial filters:

1. [severity] > 10: In SQL Server, severities from 0 to 10 are purely informational messages. Severities from 11 to 16 are errors caused by the user or the application (syntax errors, constraint violations, locking issues), and 17+ are critical system errors. By filtering for > 10, we drop the informational noise.
2. [error_number] <> 17830: Error 17830 ("Network error code 0x2746 occurred while establishing a connection") is notoriously noisy. It usually happens when a client forcefully closes a connection or a connection pool resets. Excluding it keeps our log clean and focused on actionable T-SQL errors.

The T-SQL Script

Here is the script to deploy this safety net. The configuration options were described in a previous article; you just need to be careful about the path where the files are going to be stored.

-- 1. Safely drop the session if it already exists
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'CaptureAppErrors')
BEGIN
    ALTER EVENT SESSION [CaptureAppErrors] ON SERVER STATE = STOP;
    DROP EVENT SESSION [CaptureAppErrors] ON SERVER;
END
GO
-- 2. Create the robust Extended Events session
CREATE EVENT SESSION [CaptureAppErrors] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(
        sqlserver.client_app_name, 
        sqlserver.client_hostname, 
        sqlserver.database_name, 
        sqlserver.username, 
        sqlserver.session_id, 
        sqlserver.sql_text 
    )
    WHERE (
        [severity] > 10 AND 
        [error_number] <> 17830 -- Filter out normal connection closure noise
    )
)
ADD TARGET package0.event_file(
    SET filename = N'D:\MSSQL\Logs\CaptureAppErrors.xel', -- Update this path to your environment!
    max_file_size = (50),      -- 50 MB per file
    max_rollover_files = (5)   -- Keep a history of 5 files
)
WITH (
    MAX_MEMORY = 32768 KB, -- 32 MB to accommodate massive application queries
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY = 5 SECONDS, -- Write to disk frequently
    MAX_EVENT_SIZE = 0 KB, 
    MEMORY_PARTITION_MODE = NONE, 
    TRACK_CAUSALITY = OFF, 
    STARTUP_STATE = ON -- Survive server restarts
);
GO
-- 3. Start the session
ALTER EVENT SESSION [CaptureAppErrors] ON SERVER STATE = START;
GO

Notice that in this script, we allocate a generous MAX_MEMORY (32 MB) to ensure that if a massive, dynamically generated query causes an error under high concurrency, the session has enough memory to capture the full sql_text without dropping the event. I intentionally increased the buffer size because I wanted to preserve very large dynamic SQL statements generated by ORM frameworks, which was the case when trying to capture the following error:

Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Most Extended Events sessions operate comfortably with far less memory, so you can leave MAX_MEMORY at 512 KB as in my previous article, also the MAX_FILE_SIZE at 1 MB and MAX_DISPATCH_LATENCY at 30 seconds, if capturing extremely large SQL statements is not a requirement in your environment.

Making Sense of the Noise

Once the session is running, it will silently monitor the instance with near-zero overhead. Then, when a developer complains that "a process failed but there's nothing in the app logs," you can run a simple query to shred the XML and provide them with the exact query, the error message, and the host that triggered it, as with the query below:

DECLARE @IntervalMinutes INT = 15 --Last 15 minutes only, modify at will
CREATE TABLE #Events (
  [DateTime] DATETIME, [username] NVARCHAR(128), [client_hostname] NVARCHAR(128), [client_app_name] NVARCHAR(128), [message] NVARCHAR(MAX), [sql_text] NVARCHAR(MAX));
WITH TraceFile AS (
SELECT DATEADD(MI, DATEDIFF(MI, GETUTCDATE(), GETDATE()), timestamp_utc) [DateTime], CAST(event_data AS XML) [EventData]
FROM sys.fn_xe_file_target_read_file('D:\MSSQL\Logs\CaptureAppErrors *.xel', NULL, NULL, NULL)
WHERE CAST(timestamp_utc AS DATETIME) >= DATEADD(MI, -@IntervalMinutes, GETUTCDATE())
)
INSERT INTO #Events
SELECT DateTime,
EventData.value('(/event/action[@name="username"]/value)[1]','NVARCHAR(128)') username,
EventData.value('(/event/action[@name="client_hostname"]/value)[1]','NVARCHAR(128)') client_hostname,
EventData.value('(/event/action[@name="client_app_name"]/value)[1]','NVARCHAR(128)') client_app_name,
EventData.value('(/event/data[@name="message"]/value)[1]','NVARCHAR(MAX)') message,
EventData.value('(/event/action[@name="sql_text"]/value)[1]','NVARCHAR(MAX)') sql_text
FROM TraceFile;
SELECT * FROM #Events;
DROP TABLE #Events

Real example

I noticed an error from a Web Farm Framework (WFF) server: Error 242 Severity 16 Sate 3: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The developers were not aware of it, but the application support team had been executing manual UPDATE statements for months because they believed the issue was random.

After reviewing the Extended Events output, the developer found the INSERT in the code. Within minutes, he noticed the last parameter was missing in the INSERT because it had a default value in the database, but the error was that it was not defined as NULLABLE in EntityFramework. DATETIME2 is defaulted to 0001-01-01, which is out of range for a DATETIME because it only supports years from 1753-01-01. By resolving it, several hours of manual work completely vanished forever.

Although DBAs cannot fix application code, they are often the first people capable of identifying exactly where and why the failure occurred. That information dramatically reduces the time developers spend searching for the root cause, and it’s fulfilling to be part of the solution and being able to help others with their jobs, contributing with the IT department overall.

Conclusion

In my experience, using this extended events session allowed me to capture the following errors and report them to the development team:

• The query processor ran out of internal resources and could not produce a query plan.
• Cannot insert the value NULL into column X, database Y, table Z.
• Invalid column name when performing SELECT.
• Must declare a scalar variable, within T-SQL statements.
• The INSERT statement conflicted with the FOREIGN KEY constraint when performing INSERT statements.
• The parameterized query expects a parameter which was not supplied, within T-SQL statements.
• The conversion of a data type to another resulted in an out-of-range value.
• Deadlocks

Our job as DBAs is absolutely to keep the lights on, ensure performance, and secure the data. But by leveraging tools like Extended Events to capture silent application failures, we transcend the role of "server custodians"; we become proactive problem solvers and invaluable allies to the development team.

Next time the application throws a silent error under the heavy weight of production concurrency, you won't be invisible. You'll be the person holding the exact T-SQL query that caused it, ready to help the team fix it. You can provide developers with the exact query, error message, host, and timestamp that triggered the failure.

After deploying this session, our developers started asking us to keep it permanently enabled because troubleshooting production incidents became dramatically faster. Once the development team sees the value of this data, you will never be an invisible DBA ever again.