Back in September 2024 I wrote about why the SQL Server 1205 error wasn't hitting the Error Log, and how to flip is_event_logged = 1 so it would. I ended that post saying I'd follow up with the deadlock capture itself. This is that post. Two years late. But worth the wait... 😉
The objective is simple. Capture every deadlock into a DBA table, email a notification when one occurs — and do it all without adding anything heavy to the server. No dedicated Extended Events session to maintain. No Agent job polling the server every X minutes. Nothing runs until a deadlock actually happens. Very lightweight.
What 'non-invasive' means here
The final deployment has exactly four moving parts:
| Object | Purpose |
|---|---|
| dbo.DeadlockEvents | Landing table for captured deadlocks |
| dbo.usp_CaptureDeadlock | Reads new deadlocks, logs them, emails the notification |
| DBA_CaptureDeadlock | Unscheduled Agent job that calls the proc |
| Deadlock_Alert | Agent alert on message 1205 that fires the job |
No new Extended Events session. We piggyback on system_health, the default XE session that has been running on every SQL Server instance since 2012 and already captures xml_deadlock_report. If you're unfamiliar, go get familiar. It is there right now, on every server you manage, collecting system data that you can use to help troubleshoot performance issues. How convenient is that?! I'm serious. If you don't use it, start. I cannot count the number of questions it has answered for me.
The chain of events: SQL Server's deadlock monitor picks a victim and raises error 1205. Because we ran sp_altermessage 1205 WITH_LOG in the 2024 setup, 1205 now hits the Error Log. The Agent alert fires on message 1205 and starts the DBA_CaptureDeadlock job. The job runs usp_CaptureDeadlock, which reads the system_health event file for any new deadlock events, parses victim and survivor details, logs them, and emails a notification. Purely event-driven. Nothing is running every X minutes. No deadlocks means nothing is running.
Prerequisites
Database Mail must be configured with a profile. A DBA database exists to hold the plumbing, and 1205 must be logged. That last one was the main reason behind my 2024 post -- if is_event_logged = 0 on message 1205, the Agent alert can never fire. Step 1 of the install script below checks this and fixes it if needed.
The install script
1. Confirm 1205 is logged.
IF NOT EXISTS (
SELECT 1 FROM sys.messages
WHERE message_id = 1205 AND language_id = 1033 AND is_event_logged = 1
)
BEGIN
EXEC master.sys.sp_altermessage
@message_id = 1205,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
PRINT 'Error 1205 now logged (WITH_LOG).';
END
ELSE
PRINT 'Error 1205 already logged. No change needed.';
GO
2. Create the landing table.
Columns for the parsed victim and survivor details (SPID, login, host, app, statement, lock mode), the full deadlock graph as XML, and a timestamp. Nothing fancy.
USE [DBA];
GO
IF OBJECT_ID('dbo.DeadlockEvents','U') IS NOT NULL DROP TABLE dbo.DeadlockEvents;
GO
CREATE TABLE dbo.DeadlockEvents (
DeadlockID INT IDENTITY(1,1) NOT NULL,
EventTime DATETIME2(3) NOT NULL,
DatabaseName NVARCHAR(128) NULL,
VictimSPID INT NULL,
VictimLogin NVARCHAR(128) NULL,
VictimHost NVARCHAR(128) NULL,
VictimApp NVARCHAR(256) NULL,
VictimStatement NVARCHAR(MAX) NULL,
VictimLockMode NVARCHAR(20) NULL,
VictimWaitResource NVARCHAR(256) NULL,
SurvivorSPID INT NULL,
SurvivorLogin NVARCHAR(128) NULL,
SurvivorHost NVARCHAR(128) NULL,
SurvivorApp NVARCHAR(256) NULL,
SurvivorStatement NVARCHAR(MAX) NULL,
SurvivorLockMode NVARCHAR(20) NULL,
DeadlockGraph XML NOT NULL,
DateRecorded DATETIME2(3) NOT NULL
CONSTRAINT df_DeadlockEvents_DateRecorded DEFAULT (SYSDATETIME()),
CONSTRAINT pkc_DeadlockEvents_DeadlockID PRIMARY KEY CLUSTERED (DeadlockID)
);
GO
3. Create the capture procedure.
This is where the work happens. The proc reads system_health for any xml_deadlock_report event newer than the most recent row in our table, parses victim and survivor from the XML, inserts new rows, and emails a notification. We use the latest EventTime already in the table as the cutoff for what's new. Simple Sally. Nothing else to maintain.
The victim is identified through the deadlock's victim-list/victimProcess/@id node.
CREATE OR ALTER PROCEDURE dbo.usp_CaptureDeadlock
AS
SET NOCOUNT ON;
/*
Reads system_health's event_file target for xml_deadlock_report events newer
than the most recent row in dbo.DeadlockEvents. Logs them, emails a summary.
Called by DBA_CaptureDeadlock job, which is fired by Deadlock_Alert on error 1205.
*/
BEGIN
-- Brief pause so system_health can flush the event to its file target.
WAITFOR DELAY '00:00:05';
DECLARE
@LastEventTime DATETIME2(3),
@Subject NVARCHAR(255),
@Body NVARCHAR(MAX),
@FrequencyLine NVARCHAR(200),
@CountLastHour INT,
@CountToday INT;
SELECT @LastEventTime = ISNULL(MAX(EventTime), '19000101')
FROM dbo.DeadlockEvents;
/* Pull deadlock events from the system_health file target. The
'system_health*.xel' pattern resolves relative to the default log
directory -- no path hardcoding, portable across servers. */
;WITH FileDeadlocks AS (
SELECT CAST(event_data AS XML) AS EventXML
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
),
Parsed AS (
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()),
EventXML.value('(/event/@timestamp)[1]','DATETIME2(3)')) AS EventTime,
EventXML AS FullEvent,
EventXML.value(
'(/event/data[@name="xml_report"]/value/deadlock/victim-list/victimProcess/@id)[1]',
'NVARCHAR(50)') AS VictimID
FROM FileDeadlocks
)
INSERT dbo.DeadlockEvents (
EventTime, DatabaseName,
VictimSPID, VictimLogin, VictimHost, VictimApp, VictimStatement, VictimLockMode, VictimWaitResource,
SurvivorSPID, SurvivorLogin, SurvivorHost, SurvivorApp, SurvivorStatement, SurvivorLockMode,
DeadlockGraph
)
SELECT
p.EventTime,
v.n.value('@currentdbname','NVARCHAR(128)'),
v.n.value('@spid','INT'),
v.n.value('@loginname','NVARCHAR(128)'),
v.n.value('@hostname','NVARCHAR(128)'),
v.n.value('@clientapp','NVARCHAR(256)'),
v.n.value('(inputbuf)[1]','NVARCHAR(MAX)'),
v.n.value('@lockMode','NVARCHAR(20)'),
v.n.value('@waitresource','NVARCHAR(256)'),
s.n.value('@spid','INT'),
s.n.value('@loginname','NVARCHAR(128)'),
s.n.value('@hostname','NVARCHAR(128)'),
s.n.value('@clientapp','NVARCHAR(256)'),
s.n.value('(inputbuf)[1]','NVARCHAR(MAX)'),
s.n.value('@lockMode','NVARCHAR(20)'),
p.FullEvent
FROM Parsed p
CROSS APPLY p.FullEvent.nodes(
'/event/data[@name="xml_report"]/value/deadlock/process-list/process[@id=sql:column("p.VictimID")]'
) v(n)
CROSS APPLY p.FullEvent.nodes(
'/event/data[@name="xml_report"]/value/deadlock/process-list/process[not(@id=sql:column("p.VictimID"))]'
) s(n)
WHERE p.EventTime > @LastEventTime;
IF @@ROWCOUNT = 0 RETURN;
/* Frequency context -- tells the reader whether this is a one-off
or part of a larger pattern, without opening the table. */
SELECT
@CountLastHour = SUM(CASE WHEN EventTime >= DATEADD(HOUR, -1, SYSDATETIME()) THEN 1 ELSE 0 END),
@CountToday = SUM(CASE WHEN EventTime >= CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(3)) THEN 1 ELSE 0 END)
FROM dbo.DeadlockEvents;
SET @FrequencyLine =
CAST(@CountLastHour AS NVARCHAR(10)) + ' in the last hour, '
+ CAST(@CountToday AS NVARCHAR(10)) + ' today.';
SET @Body =
'<html><body style="font-family:Verdana;font-size:11px;color:#000080;">'
+ '<h3>Deadlock captured on ' + @@SERVERNAME + '</h3>'
+ '<p style="color:#800000;"><b>Frequency:</b> ' + @FrequencyLine + '</p>';
SELECT @Body = @Body
+ '<p><b>Event time:</b> ' + CONVERT(NVARCHAR(30), EventTime, 120) + '<br/>'
+ '<b>Database:</b> ' + ISNULL(DatabaseName,'-') + '<br/>'
+ '<b>Victim SPID:</b> ' + ISNULL(CAST(VictimSPID AS NVARCHAR(10)),'-')
+ ' (' + ISNULL(VictimLogin,'-') + ')<br/>'
+ '<b>Victim statement:</b><br/>'
+ '<pre style="font-family:Consolas;font-size:11px;">'
+ ISNULL(VictimStatement,'-') + '</pre>'
+ '<b>Survivor SPID:</b> ' + ISNULL(CAST(SurvivorSPID AS NVARCHAR(10)),'-')
+ ' (' + ISNULL(SurvivorLogin,'-') + ')</p>'
FROM dbo.DeadlockEvents
WHERE EventTime > @LastEventTime
ORDER BY EventTime DESC;
SET @Body = @Body + '<p>Full graph in <b>DBA.dbo.DeadlockEvents</b>.</p></body></html>';
SET @Subject = @@SERVERNAME + ' - Deadlock captured';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfile', -- <<< update
@recipients = 'DBATeam@YourCompany.com', -- <<< update
@subject = @Subject,
@body = @Body,
@body_format = 'HTML',
@importance = 'High';
END
SET NOCOUNT OFF;
GO
4. Create the unscheduled Agent job.
It does nothing on its own. No schedule. It runs only when fired by the 1205 alert in the next step.
USE [msdb];
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'DBA_CaptureDeadlock')
EXEC msdb.dbo.sp_delete_job @job_name = N'DBA_CaptureDeadlock';
GO
DECLARE @jobId BINARY(16);
EXEC msdb.dbo.sp_add_job
@job_name = N'DBA_CaptureDeadlock',
@enabled = 0,
@description = N'Fired by Deadlock_Alert. Reads system_health for new xml_deadlock_report events, logs to DBA.dbo.DeadlockEvents, emails a summary. Not scheduled.',
@category_name = N'[Uncategorized (Local)]',
@owner_login_name = N'sa',
@job_id = @jobId OUTPUT;
DECLARE @Log_Path VARCHAR(255)
SELECT @Log_Path =REPLACE(convert(varchar(1000),SERVERPROPERTY('ErrorLogFileName')),'\ERRORLOG','\')
SET @Log_Path = @Log_Path + 'DBA_CaptureDeadlock_$(ESCAPE_SQUOTE(STRTDT)).txt'
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobId,
@step_name = N'Capture',
@step_id = 1,
@subsystem = N'TSQL',
@command = N'EXEC dbo.usp_CaptureDeadlock;',
@database_name = N'DBA',
@output_file_name = @Log_Path,
@on_success_action = 1,
@on_fail_action = 2;
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
GO
5. Create the alert on message 1205.
SQL Server only allows one alert per message_id, so the script first clears out any pre-existing alert on 1205 -- whatever name it goes by -- before creating this one.
DECLARE @ExistingAlert NVARCHAR(128);
SELECT @ExistingAlert = name FROM msdb.dbo.sysalerts WHERE message_id = 1205;
IF @ExistingAlert IS NOT NULL
EXEC msdb.dbo.sp_delete_alert @name = @ExistingAlert;
GO
EXEC msdb.dbo.sp_add_alert
@name = N'Deadlock_Alert',
@message_id = 1205,
@severity = 0,
@enabled = 0,
@delay_between_responses = 0,
@include_event_description_in = 0,
@job_name = N'DBA_CaptureDeadlock';
GO
6. Enable the alert and job.
EXEC msdb.dbo.sp_update_job @job_name = N'DBA_CaptureDeadlock', @enabled = 1; EXEC msdb.dbo.sp_update_alert @name = N'Deadlock_Alert', @enabled = 1; GO
One gotcha worth knowing: file target, not ring buffer
The canonical Microsoft example for reading deadlocks from system_health uses the ring_buffer target. It looks clean, it's published in Microsoft's own docs, but it does not work reliably on SQL Server 2022. On my 2022 build, a query against system_health's ring_buffer target returns no rows at all for xml_deadlock_report events, but the same events are visible immediately in the file target. I tested and retested, and tested again.
This is a known issue with how the ring_buffer target serializes events. Jonathan Kehayias has a memorably-titled post about it: Why I hate the ring_buffer target in Extended Events. In short, use the file target. The proc above reads system_health*.xel directly through sys.fn_xe_file_target_read_file, and the file name pattern is resolved relative to the default log directory. No path hardcoding so it is easily usable across servers.
Test it
Create a tiny table, then run two sessions that update its rows in opposite order with HOLDLOCK. One will be chosen as the victim and killed. The alert fires, the job runs, the email notification is sent.
USE DBA;
IF OBJECT_ID('dbo.DeadlockTest','U') IS NOT NULL
DROP TABLE dbo.DeadlockTest;
CREATE TABLE dbo.DeadlockTest (ID INT PRIMARY KEY, Val INT);
INSERT INTO dbo.DeadlockTest (ID, Val) VALUES (1, 0), (2, 0);
GO
Session 1 (first query window):
USE DBA; BEGIN TRAN; UPDATE dbo.DeadlockTest WITH (HOLDLOCK) SET Val = 1 WHERE ID = 1; WAITFOR DELAY '00:00:05'; UPDATE dbo.DeadlockTest SET Val = 1 WHERE ID = 2; COMMIT;
Session 2 (second query window, kicked off within the 5-second window):
USE DBA; BEGIN TRAN; UPDATE dbo.DeadlockTest WITH (HOLDLOCK) SET Val = 2 WHERE ID = 2; WAITFOR DELAY '00:00:05'; UPDATE dbo.DeadlockTest SET Val = 2 WHERE ID = 1; COMMIT;
One session dies with the familiar 1205:
Msg 1205, Level 13, State 51, Line 4 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Verify
Confirm the alert fired, the job ran, and the row landed.
-- Did the alert fire? SELECT name, last_occurrence_date, last_occurrence_time, occurrence_count FROM msdb.dbo.sysalerts WHERE name = 'Deadlock_Alert'; -- Did the job run? SELECT TOP 5 run_date, run_time, run_status, message FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE j.name = 'DBA_CaptureDeadlock' ORDER BY h.run_date DESC, h.run_time DESC; -- And most importantly -- did we capture it? SELECT TOP 5 * FROM DBA.dbo.DeadlockEvents ORDER BY EventTime DESC;
What the email looks like
Purposely minimal. Just enough for triage at a glance, with a frequency line to signal whether this is a one-off or a pattern. And the full deadlock graph is in the table with details for your review.
More to Read
Where is the 1205 error for the SQL Server Deadlock?!! -- the prerequisite post. 1205 has to be logged for the alert to fire.
SQL Server Deadlock Notifications -- an older post of mine using Service Broker and Event Notifications. Still valid, different architecture. Use whichever fits your environment.
MSSQLSERVER_1205 (Microsoft Learn) -- the official reference on error 1205.
Use the system_health session (Microsoft Learn) -- the default XE session we piggyback on.
