Confirm Error 1205 Is Logged to the Error Log

This is the prerequisite that makes the entire chain work. If 1205 is not logged, the alert cannot fire.

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 is now set to log to the Error Log.';
END
ELSE
    PRINT 'Error 1205 already logs to the Error Log. No change needed.';
GO

Create the Deadlock Landing Table

Stores each deadlock event with both victim and survivor details, the query text from each side, and the complete deadlock graph as XML for deep analysis.

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,

    -- Victim details (the session SQL Server killed)
    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,

    -- Survivor details (the session that continued)
    SurvivorSPID       INT            NULL,
    SurvivorLogin      NVARCHAR(128)  NULL,
    SurvivorHost       NVARCHAR(128)  NULL,
    SurvivorApp        NVARCHAR(256)  NULL,
    SurvivorStatement  NVARCHAR(MAX)  NULL,
    SurvivorLockMode   NVARCHAR(20)   NULL,

    -- Full deadlock graph for deep analysis
    DeadlockGraph      XML            NOT NULL,

    DateRecorded       DATETIME2(3)   NOT NULL
        CONSTRAINT df_DeadlockEvents_DateRecorded DEFAULT (SYSDATETIME()),

    CONSTRAINT pkc_DeadlockEvents_DeadlockID
        PRIMARY KEY CLUSTERED (DeadlockID)
);
GO

Create the Capture Procedure

This procedure does all the real work. It uses the most recent EventTime in the table as a cutoff — anything newer than that in system_health is a new event to process. It parses victim and survivor from the deadlock XML, inserts the rows, and sends a concise HTML email with a frequency line so you can tell immediately whether this is a one-off or part of a larger pattern.

Update these two values before creating the procedure:

• 'SQLMailProfile' — change to your actual Database Mail profile name
• 'DBATeam@YourCompany.com' — change to your DBA team email address

USE [DBA];
GO

CREATE OR ALTER PROCEDURE dbo.usp_CaptureDeadlock
AS
SET NOCOUNT ON;
/*
    usp_CaptureDeadlock
    -------------------
    Reads system_health's .xel file target for xml_deadlock_report events
    newer than the most recent row in dbo.DeadlockEvents.
    Parses victim and survivor detail, inserts new rows, emails a notification.

    Called by DBA_CaptureDeadlock Agent job, which is fired by Deadlock_Alert
    on SQL Server error 1205. Nothing polls this procedure -- it runs only
    when a deadlock actually occurs.
*/
BEGIN
    -- Brief pause to allow system_health to flush the new event to the file target
    WAITFOR DELAY '00:00:05';

    DECLARE
        @LastEventTime DATETIME2(3),
        @Subject       NVARCHAR(255),
        @Body          NVARCHAR(MAX),
        @FrequencyLine NVARCHAR(200),
        @CountLastHour INT,
        @CountToday    INT;

    -- Use the most recent captured event as the cutoff for what is new
    SELECT @LastEventTime = ISNULL(MAX(EventTime), '19000101')
    FROM dbo.DeadlockEvents;

    -- -------------------------------------------------------
    -- Read new deadlock events from system_health file target
    -- system_health*.xel resolves to the instance log directory
    -- automatically -- no hardcoded paths, works on any server
    -- -------------------------------------------------------
    ;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;

    -- Nothing new to report -- exit cleanly
    IF @@ROWCOUNT = 0 RETURN;

    -- -------------------------------------------------------
    -- Build frequency context for the email
    -- Tells the reader at a glance if this is isolated or a pattern
    -- -------------------------------------------------------
    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.';

    -- -------------------------------------------------------
    -- Build and send the HTML email notification
    -- -------------------------------------------------------
    SET @Body =
        N''
      + N'
Deadlock captured on ' + @@SERVERNAME + N'
'
      + N'
Frequency: ' + @FrequencyLine + N'
';

    SELECT @Body = @Body
        + N'
Event time: '   + CONVERT(NVARCHAR(30), EventTime, 120)       + N'
'
        + N'Database: '         + ISNULL(DatabaseName, '-')                  + N'
'
        + N'Victim SPID: '      + ISNULL(CAST(VictimSPID AS NVARCHAR(10)), '-')
        + N' (' + ISNULL(VictimLogin, '-') + N' / ' + ISNULL(VictimHost, '-') + N')
'
        + N'Victim statement:
'
        + N'
'
            + ISNULL(VictimStatement, '-') + N'
'
        + N'Survivor SPID: '    + ISNULL(CAST(SurvivorSPID AS NVARCHAR(10)), '-')
        + N' (' + ISNULL(SurvivorLogin, '-') + N')
'
        + N'Survivor statement:
'
        + N'
'
            + ISNULL(SurvivorStatement, '-') + N'
'
    FROM dbo.DeadlockEvents
    WHERE EventTime > @LastEventTime
    ORDER BY EventTime DESC;

    SET @Body = @Body
        + N'
Full deadlock graph stored in DBA.dbo.DeadlockEvents.
'
        + N'';

    SET @Subject = @@SERVERNAME + N' - Deadlock captured';

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name  = 'SQLMailProfile',           -- UPDATE THIS
         @recipients    = 'DBATeam@YourCompany.com',  -- UPDATE THIS
         @subject       = @Subject,
         @body          = @Body,
         @body_format   = 'HTML',
         @importance    = 'High';
END;
GO

Create the Unscheduled Agent Job

This job has no schedule. It sits idle until fired by the alert in Step 5. The output file is written to the SQL log directory alongside the system_health files for easy access.

USE [msdb];
GO

-- Clean up if a previous version exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DBA_CaptureDeadlock')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DBA_CaptureDeadlock';
GO

DECLARE @jobId   BINARY(16);
DECLARE @LogPath VARCHAR(255);

-- Build output log path in the SQL log directory
SELECT @LogPath = REPLACE(CONVERT(VARCHAR(1000), SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG', '\');
SET @LogPath = @LogPath + 'DBA_CaptureDeadlock_$(ESCAPE_SQUOTE(STRTDT)).txt';

EXEC msdb.dbo.sp_add_job
     @job_name         = N'DBA_CaptureDeadlock',
     @enabled          = 0,                  -- enabled in Step 6
     @description      = N'Fired by Deadlock_Alert on error 1205. Reads system_health for new deadlock events, logs to DBA.dbo.DeadlockEvents, and emails a summary. Not scheduled.',
     @category_name    = N'[Uncategorized (Local)]',
     @owner_login_name = N'sa',
     @job_id           = @jobId OUTPUT;

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  = @LogPath,
     @on_success_action = 1,   -- Quit with success
     @on_fail_action    = 2;   -- Quit with failure

EXEC msdb.dbo.sp_add_jobserver
     @job_id     = @jobId,
     @server_name = N'(local)';
GO

Create the Alert on Error 1205

SQL Server only allows one alert per message_id. The script removes any pre-existing alert on 1205 before creating this one — regardless of what it was named.

USE [msdb];
GO

-- Remove any existing alert on message 1205 (regardless of name)
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,          -- enabled in Step 6
     @delay_between_responses      = 0,
     @include_event_description_in = 0,
     @job_name                     = N'DBA_CaptureDeadlock';
GO

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