The Time I Set Up a Deadlock Alert That Emails You the Actual Deadlock Graph

This one's from a few years back, but it's still one of my favorite "small plumbing, big payoff" setups. The ask was simple on the surface: "can we get notified when a deadlock happens?" Sure, easy - SQL Server can alert on that natively. But the follow-up question is the one that actually matters: notified with what? "A deadlock happened at 2:14 AM" is technically true and completely useless. Nobody can do anything with that sentence. What people actually need to fix a deadlock is the deadlock graph itself - which process was the victim, which process was holding what lock, and the exact T-SQL each side was running.

So that was the goal: the second a deadlock fires, someone should get an email in their inbox with the real deadlock XML sitting right there as an attachment, ready to open in SSMS's deadlock graph viewer. No digging through logs, no asking "hey did anyone see a deadlock alert," no logging into the server at all if you don't want to.

The three moving pieces

This is built from three parts that only work because they hand off to each other in sequence:

1. An Extended Events session that's always running in the background, quietly capturing the full XML of every deadlock the instance produces.
2. A SQL Server Agent Alert tied to the Number of Deadlocks/sec performance counter, so the moment that counter ticks above zero, SQL Server itself fires a response.
3. A stored procedure that the alert's response job calls, which reads the freshly captured deadlock XML out of the event file, logs it to a permanent table for history, and fires off the email with the XML attached.

Example status: this article preserves a historical implementation. The ordering and startup examples have been corrected, but the full pipeline has not been lab-tested during this update. Review the limitations below before adapting it.

Piece 1: the Extended Events session

Nothing fancy here - this is the standard way to capture deadlocks without the overhead of the older trace-based approaches. It just sits there running (STARTUP_STATE=ON, so it survives a restart) and writes each deadlock's full XML report to a rotating file target:

CREATE EVENT SESSION [Collect-Deadlock] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(
        package0.collect_system_time,
        sqlos.task_time,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.is_system,
        sqlserver.username
    )
)
ADD TARGET package0.event_file(
    SET filename = N'Collect-Deadlock.xel',
        max_file_size = (100),
        max_rollover_files = (10)
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 20 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION [Collect-Deadlock] ON SERVER STATE = START;
GO

The max_rollover_files = 10 bit matters in production - deadlocks tend to cluster (one bad query plan can cause a burst of them), and you don't want the very first file to get overwritten before your alert job has had a chance to read it.

Piece 2: the SQL Agent Alert

This is the part people forget SQL Server can just do natively - no polling job needed to detect a deadlock. SQL Server Agent can watch the SQLServer:Locks performance object directly and fire the moment the counter moves:

Alert type:   SQL Server Performance Condition Alert
Object:       SQLServer:Locks
Counter:      Number of Deadlocks/sec
Instance:     _Total
Condition:    rises above 0

Response:     Execute job "Deadlock Alert"

And the "Deadlock Alert" job itself is deliberately tiny:

WAITFOR DELAY '00:00:20';
EXEC DBATools.dbo.sp_deadlock_extended_events;

That 20-second delay isn't decoration - it's there because MAX_DISPATCH_LATENCY = 20 SECONDS on the event session means the deadlock XML might not be flushed to the file yet the instant the performance counter ticks. Skip the delay and you risk the stored procedure running a beat too early and finding nothing to read. This was one of those "why is my job sometimes missing the very deadlock that triggered it" moments before I added the wait.

Piece 3: the stored procedure - read, parse, log, email

This is where most of the actual work happens, and it's worth walking through in order because each step solves a specific problem.

Step A - pull unprocessed deadlocks out of the event file. sys.fn_xe_file_target_read_file reads straight from the .xel files (the * wildcard picks up all rollover files), filtered down to just the deadlock report events:

CREATE TABLE #DeadLockXMLData (DeadLockXMLData XML, DeadLockNumber INT);
CREATE TABLE #DeadLockDetails (
    ProcessID VARCHAR(50), HostName VARCHAR(50), LoginName VARCHAR(100),
    ClientApp VARCHAR(100), Frame NVARCHAR(MAX), TSQLString NVARCHAR(MAX),
    DeadLockDateTime DATETIME, IsVictim TINYINT, DeadLockNumber INT
);

INSERT INTO #DeadLockXMLData (DeadLockXMLData, DeadLockNumber)
SELECT TOP 20
    CONVERT(XML, event_data),
    ROW_NUMBER() OVER (ORDER BY CONVERT(XML, event_data).value('(/event/@timestamp)[1]', 'datetime2'), file_name, file_offset)
FROM sys.fn_xe_file_target_read_file(N'Collect-Deadlock*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
ORDER BY 2 DESC;

Step B - loop through each deadlock and parse it with OPENXML. A single burst can produce multiple deadlock events, so this cursors through all of them rather than assuming there's only one:

DECLARE @DeadLockXMLData XML, @DeadLockNumber INT, @Document INT;
DECLARE curDeadlocks CURSOR FOR
    SELECT DeadLockXMLData, DeadLockNumber FROM #DeadLockXMLData;

OPEN curDeadlocks;
FETCH NEXT FROM curDeadlocks INTO @DeadLockXMLData, @DeadLockNumber;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_xml_preparedocument @Document OUTPUT, @DeadLockXMLData;

    INSERT INTO #DeadLockDetails
        (ProcessID, HostName, LoginName, ClientApp, Frame, TSQLString, DeadLockDateTime, DeadLockNumber)
    SELECT ProcessID, HostName, LoginName, ClientApp, Frame, TSQL, LastBatchCompleted, @DeadLockNumber
    FROM OPENXML(@Document, 'event/data/value/deadlock/process-list/process')
    WITH (
        ProcessID  VARCHAR(50)   '@id',
        HostName   VARCHAR(50)   '@hostname',
        LoginName  VARCHAR(50)   '@loginname',
        ClientApp  VARCHAR(50)   '@clientapp',
        TSQL       NVARCHAR(4000) 'inputbuf',
        Frame      NVARCHAR(4000) 'executionStack/frame',
        LastBatchCompleted NVARCHAR(50) '@lastbatchcompleted'
    );

    -- Flag whichever process SQL Server picked as the victim, so it's
    -- visually distinct from the process that "won" and kept running.
    UPDATE #DeadLockDetails
    SET IsVictim = 1
    WHERE DeadLockNumber = @DeadLockNumber
      AND ProcessID IN (
        SELECT ProcessID
        FROM OPENXML(@Document, 'event/data/value/deadlock/victim-list/victimProcess')
        WITH (ProcessID VARCHAR(50) '@id')
    );

    EXEC sp_xml_removedocument @Document;
    FETCH NEXT FROM curDeadlocks INTO @DeadLockXMLData, @DeadLockNumber;
END

CLOSE curDeadlocks;
DEALLOCATE curDeadlocks;

Step C - historical process-level deduplication. The original snippet below checks process IDs, which do not uniquely identify deadlock events. It can omit later events with reused identifiers. A production collector must persist the source file name and offset, plus event timestamp, and deduplicate by event before inserting its processes. The TOP 20 batch above also needs a durable checkpoint and backlog handling to avoid dropping bursts:

INSERT INTO DBATools.dbo.DeadlockDetails
SELECT * FROM #DeadLockDetails d
WHERE NOT EXISTS (
    SELECT 1 FROM DBATools.dbo.DeadlockDetails dd WHERE d.ProcessID = dd.ProcessID
);

Step D - get the raw XML out to a file, and email it. This is the step that took a couple of iterations to get right. My first attempt just queried a summary (victim T-SQL vs. blocking T-SQL as text columns) and sent that as the attachment via sp_send_dbmail's @attach_query_result_as_file option - readable, but it lost the actual graph structure, and anyone debugging it still had to reconstruct the deadlock shape from a paragraph of text. What people actually wanted was the real XML they could drop into SSMS and get the visual deadlock graph, resource nodes and all. The historical command below illustrates that approach; it is not an executable recommendation. It has multiple quoted sections despite xp_cmdshell's documented quoting restriction, no explicit target instance, and no successful-export check before attaching a fixed filename. A failed export could send stale data. Keep xp_cmdshell disabled for a replacement design.

DECLARE @query NVARCHAR(MAX) = N'
    SET NOCOUNT ON;
    SELECT CONVERT(XML, a.DeadLockXMLData)
    FROM (
        SELECT TOP 1 CONVERT(XML, event_data) AS DeadLockXMLData,
               ROW_NUMBER() OVER (ORDER BY CONVERT(XML, event_data).value(''(/event/@timestamp)[1]'', ''datetime2''), file_name, file_offset) AS DeadLockNumber
        FROM sys.fn_xe_file_target_read_file(N''Collect-Deadlock*.xel'', NULL, NULL, NULL)
        WHERE object_name = ''xml_deadlock_report''
        ORDER BY 2 DESC
    ) a';

DECLARE @sql VARCHAR(8000) =
    'bcp "' + @query + '" queryout "D:\DBAlerts\Deadlocks\LatestDeadlock.xml" -c -t"*|*" -T';

EXEC master..xp_cmdshell @sql;

DECLARE @servername NVARCHAR(150) = @@SERVERNAME;
DECLARE @mysubject  NVARCHAR(200) = 'Deadlock Alert with XML - ' + @servername;

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Server DB Alerts',
    @recipients   = N'[email protected]',
    @subject      = @mysubject,
    @body         = 'PFA',
    @file_attachments = 'D:\DBAlerts\Deadlocks\LatestDeadlock.xml';

For a replacement, use a least-privilege SQL Agent PowerShell/CmdExec step with an explicit SQL instance, unique per-event output filename, restricted directory ACLs, checked export status, and a freshness check before email delivery. Define retention and cleanup. Real deadlock XML can contain query literals, login names, and hosts, so review/redact it and restrict recipients. These are requirements for a lab-validated replacement, not a claim that the historical code implements them.

The synthetic email below shows the intended presentation:

Open that XML in SSMS (Query menu > Open Deadlock Graph, or just double-click a .xdl-renamed copy) and you get the classic picture - two processes, each holding the resource the other one needs:

(Both images use fabricated demo data - generic server, host, and login names. The real schema and code structure are illustrative.)

Things I'd flag or do differently today

I built this a few years back, and looking back at it honestly, there are a few things in here I wouldn't ship as-is today, and I'd rather call them out than pretend the script is perfect:

• xp_cmdshell is a real attack surface. Using bcp via xp_cmdshell to dump the XML to disk works, but xp_cmdshell shells out to the OS, and it should be enabled only on instances where that's genuinely locked down and audited - not left on by default. If I rebuilt this today, I'd look at generating the file with PowerShell called from the Agent job step instead (a CmdExec job step type), keeping xp_cmdshell off entirely.
• Hardcoded recipients and profile names. The mail profile and recipient list are baked straight into the procedure. Moving those into a small config table (or SQL Agent job step parameters) makes it a lot easier to update the distribution list without editing and redeploying the stored procedure itself.
• Use durable event identity. A deadlock graph's process ID is an XML identifier, not the same thing as its spid attribute. Neither identifies an event across files. Persist file_name and file_offset from the event reader, along with the event's UTC timestamp, and deduplicate at the event level. The historical snippets above still use process-level deduplication and are not a complete ingestion implementation.
• Distinguish event time from batch time. lastbatchstarted is the batch's start time, not when the deadlock occurred. Use /event/@timestamp for incident chronology. Selecting the latest event alone can omit earlier events in a burst; process all events after a durable checkpoint.
• No retention/cleanup on DeadlockDetails. Same story as most audit tables I've built - it grows forever unless something prunes it. I'd add a purge job for anything older than a defined window.
• The commented-out first attempt is still in the file, which is honestly very "real DBA script energy" - I kept it there as a note to future-me about why the final version looks the way it does, rather than cleaning it up. Sometimes the commented-out failed attempt is more useful documentation than a clean comment would have been.

Why this was worth building

The instinct to "just get an alert when X happens" is usually only half the job. The alert itself is easy - SQL Server's had performance condition alerts forever. The part that actually helps someone at 2 AM is making sure the evidence travels with the notification, not just the fact that something went wrong. A deadlock alert that says "a deadlock happened, good luck" gets ignored after the third time. A deadlock alert that hands you the actual graph, the victim, and the blocking query - ready to open - gets used.