SQL Server Deadlock Alerts: Why They Do Not Fire and How to Fix Them
You set up a SQL Server Agent alert for error 1205. You tested it by generating a deadlock. No email arrived. You checked the operator, confirmed Database Mail works, verified the alert is enabled, and tried again. Still nothing. The alert history shows zero occurrences even though the deadlock definitely happened.
This is one of the most commonly reported “broken” alert setups in SQL Server, and the fix is not where most DBAs look. The problem is not your operator, not your mail profile, and not your alert definition. The problem is that error 1205 is not written to the Windows Application Event Log by default on SQL Server 2016 and later, so the SQL Agent alert never sees it and never fires.
This article explains exactly why that happens, the one-line fix that makes it work, two additional silent failure modes that catch DBAs by surprise, and the Extended Events approach as the modern production-grade alternative. Everything here works on SQL Server 2016, 2017, 2019, 2022, and 2025.
Compatibility: SQL Server 2016 through SQL Server 2025. All T-SQL in this article uses only built-in system stored procedures and DMVs available in all versions. The Extended Events approach in Section 5 uses xml_deadlock_report which has been available since SQL Server 2012.
- What a Deadlock Is and What Error 1205 Means
- Why the SQL Agent Alert Never Fires
- The Two Other Silent Failure Modes
- Step 1: Enable Event Logging for Error 1205
- Step 2: Set Up the SQL Agent Alert and Notification
- Step 3: Add Trace Flags for Deadlock Detail in the Error Log
1 What a Deadlock Is and What Error 1205 Means Beginner
A deadlock occurs when two or more sessions are each holding a lock that the other needs, creating a circular dependency that neither session can resolve on its own. Session A holds a lock on Table 1 and wants Table 2. Session B holds a lock on Table 2 and wants Table 1. Neither can proceed and neither will voluntarily release its lock.
SQL Server’s lock monitor detects this cycle automatically, typically within five seconds. It selects one session as the deadlock victim, rolls back that session’s transaction, and raises error 1205 to the victim’s connection. The other session continues and completes normally. The victim receives the message: “Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
Error 1205 is severity 13. It is a user-facing error that tells the application its transaction was killed and it should retry. It is also the error number that SQL Server Agent alerts monitor to detect deadlock events. The problem is what happens between the deadlock occurring and the alert being evaluated.
2 Why the SQL Agent Alert Never Fires Beginner
SQL Server Agent alerts for error numbers work by monitoring the Windows Application Event Log. When SQL Server writes an error to the Windows Application Event Log, the SQL Agent alert system sees it, matches it against configured alerts, and sends notifications. The key word is “writes.”
Not every SQL Server error is written to the Windows Application Event Log. Each error message in SQL Server has a property called is_event_logged that controls whether the error gets written to the Windows log when it occurs. You can check this for error 1205 right now:
-- Check whether error 1205 is configured to write to the Windows Event Log
SELECT
message_id,
severity,
is_event_logged,
text
FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033; -- English
-- is_event_logged = 0 means the error is NOT written to the Windows Event Log
-- is_event_logged = 1 means it IS written -- and the alert will fireOn SQL Server 2016 and later, error 1205 ships with is_event_logged = 0 by default. The deadlock happens, the victim receives the error, but nothing is written to the Windows Application Event Log. The SQL Agent alert monitors the Windows Event Log, sees no entry for 1205, and never fires. Your alert definition is correct. Your operator is correct. Your mail profile works. The error is simply never written to the log the alert is watching.
This is the root cause that most forum posts and documentation miss. Enabling trace flags 1204 or 1222, which write deadlock detail to the SQL Server Error Log, does not fix this problem. Those trace flags write to a different log than the one SQL Agent monitors. Disabling and re-enabling the alert does not fix it. Recreating the operator does not fix it. The only fix is enabling event logging for error 1205.
3 The Two Other Silent Failure Modes Intermediate
Even after you fix the is_event_logged problem, two other issues can cause the alert to silently do nothing. Both appear to be working but produce no notifications.
Silent Failure 1: The Operator Lookup Returns NULL
The severity alerts script on SQLYARD uses a dynamic operator lookup to find the right operator name:
-- The operator lookup from the existing severity alerts script
DECLARE @OPERATOR SYSNAME
SET @OPERATOR = (
SELECT TOP 1 name
FROM msdb.dbo.sysoperators
WHERE LOWER(email_address) IN (
'Add your Email',
'Add your Email',
'Add your Email'
)
)
IF @OPERATOR IS NOT NULL
BEGIN
-- alert creation code runs here
ENDIf the three placeholder values ('Add your Email') were never replaced with real email addresses, the SELECT returns NULL. The variable @OPERATOR is NULL. The IF @OPERATOR IS NOT NULL check fails. The entire alert creation block is skipped silently. No error is raised. The script appears to complete successfully but no alert and no notification were created.
Always verify the operator lookup returned a value before assuming the alert was created. Run the SELECT statement manually with your actual email addresses before running the full script. If it returns NULL, create the operator first using the instructions in Section 5.
Silent Failure 2: Alert Exists but Has No Notification
An alert without a notification attached to it will detect the event and record it in the alert history but will never send an email. The alert and the notification are two separate objects in msdb. Creating the alert is not sufficient. The sp_add_notification call must also succeed.
-- Check: does the alert exist AND does it have a notification attached?
SELECT
a.name AS AlertName,
a.message_id,
a.enabled,
a.last_occurrence_date,
a.occurrence_count,
o.name AS OperatorName,
o.email_address AS OperatorEmail,
n.notification_method AS NotifyMethod
-- notification_method: 1=Email, 2=Pager, 4=NetSend, 7=All
FROM msdb.dbo.sysalerts a
LEFT JOIN msdb.dbo.sysnotifications n ON n.alert_id = a.id
LEFT JOIN msdb.dbo.sysoperators o ON o.id = n.operator_id
WHERE a.message_id IN (1205, 1211)
ORDER BY a.name;
-- If OperatorName is NULL for an alert, the alert exists but has no notification
-- It will detect the event but never send an email4 Step 1: Enable Event Logging for Error 1205 Beginner
This is the fix that makes the SQL Agent alert work. One command, run once on each SQL Server instance where you want deadlock alerts. It tells SQL Server to write error 1205 to the Windows Application Event Log whenever a deadlock occurs, which is what the SQL Agent alert system monitors.
-- Enable Windows Application Event Log writing for error 1205
-- Run on each SQL Server instance where deadlock alerts are needed
-- Requires sysadmin role
-- Compatible with: SQL Server 2016, 2017, 2019, 2022, 2025
EXEC master.sys.sp_altermessage
@message_id = 1205,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
GO
-- Verify the change took effect:
SELECT
message_id,
severity,
is_event_logged,
text
FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033;
-- is_event_logged should now show 1
-- This change persists across SQL Server restarts -- it is stored in the catalogThis change is permanent and persists across restarts. It is stored in the SQL Server message catalog, not in a trace flag or session setting. You run it once and it stays. It does not need to be added to a startup job or applied again after a service restart or failover.
Also enable event logging for error 1211 while you are here. Error 1211 covers lock timeout situations related to deadlock scenarios and is worth alerting on alongside 1205:
-- Also enable event logging for error 1211 (lock timeout/deadlock-related)
EXEC master.sys.sp_altermessage
@message_id = 1211,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
GO
-- Verify both:
SELECT message_id, is_event_logged, text
FROM sys.messages
WHERE message_id IN (1205, 1211)
AND language_id = 1033;5 Step 2: Set Up the SQL Agent Alert and Notification Intermediate
With event logging enabled, you can now create the SQL Agent alert and attach a notification to it. If you already ran the severity alerts script from SQLYARD, the alert may already exist but without a notification. Check first, then create what is missing.
Check and Create the Operator
-- Check whether a DBA operator already exists
SELECT name, email_address, is_enabled = enabled
FROM msdb.dbo.sysoperators
ORDER BY name;
-- If no operator exists, create one now
-- Replace the name and email with your actual DBA team details
EXEC msdb.dbo.sp_add_operator
@name = N'DBA Team',
@enabled = 1,
@email_address = N'dba@yourcompany.com';
-- Verify the operator was created:
SELECT name, email_address
FROM msdb.dbo.sysoperators
WHERE name = N'DBA Team';Create the Deadlock Alert
-- Create the SQL Agent alert for deadlock error 1205
-- If the alert already exists from the severity alerts script, skip this block
-- Check first:
SELECT name, message_id, enabled
FROM msdb.dbo.sysalerts
WHERE message_id = 1205;
-- Create if it does not exist:
EXEC msdb.dbo.sp_add_alert
@name = N'Deadlock - Error 1205',
@message_id = 1205,
@severity = 0, -- 0 = use message_id, not severity level
@enabled = 1,
@delay_between_responses = 60, -- seconds between repeat notifications
-- prevents alert storms if deadlocks are frequent
@include_event_description_in = 1; -- include the event text in the notification
-- Create the notification linking the alert to the operator
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Deadlock - Error 1205',
@operator_name = N'DBA Team', -- must match the operator name exactly
@notification_method = 7; -- 7 = email + pager + net send (use 1 for email only)
-- Also create the alert for error 1211:
EXEC msdb.dbo.sp_add_alert
@name = N'Deadlock - Error 1211',
@message_id = 1211,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1;
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Deadlock - Error 1211',
@operator_name = N'DBA Team',
@notification_method = 7;The @delay_between_responses parameter matters on busy systems. If deadlocks are occurring frequently (multiple per minute), setting this to 0 will send an email for every single deadlock and flood the DBA inbox. Set it to 60 seconds minimum for most environments. On very busy systems, 300 seconds (5 minutes) is reasonable. The alert still records every occurrence in its history, so you can review the count even if not every occurrence sent an email.
6 Step 3: Add Trace Flags for Deadlock Detail in the Error Log Intermediate
The SQL Agent alert tells you a deadlock happened. It does not tell you which queries were involved, which resources were contested, or which session was the victim. To get that detail written to the SQL Server Error Log automatically when a deadlock occurs, enable one or both of these trace flags.
| Trace Flag | What It Writes | Detail Level | Recommendation |
|---|---|---|---|
1222 |
Deadlock detail organized by process and resource in XML-style format | High, readable | Preferred for production use |
1204 |
Deadlock detail organized by node (older format) | Medium | Use if 1222 output is too verbose for your environment |
-- Enable trace flag 1222 globally (writes deadlock detail to SQL Server Error Log)
-- -1 means global (all sessions), not just the current session
DBCC TRACEON (1222, -1);
-- Verify it is active:
DBCC TRACESTATUS (1222);
-- TraceFlag Status Global Session
-- 1222 1 1 0
-- Status = 1 means it is enabled globally
-- To also enable 1204 (both can run together):
-- DBCC TRACEON (1204, -1);
-- Make it permanent across restarts by adding as a startup parameter:
-- In SQL Server Configuration Manager:
-- SQL Server (MSSQLSERVER) > Properties > Startup Parameters
-- Add: -T1222
-- Restart the service for the startup parameter to take effect
-- Verify a trace flag was added as a startup parameter (SQL Server 2016+):
SELECT value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%MSSQLServer\Parameters%'
AND value_name LIKE 'SQLArg%'
ORDER BY value_name;Trace flag 1222 is the preferred choice over 1204 because its output format is more structured and easier to parse. Both write to the SQL Server Error Log (viewable in SSMS under Management > SQL Server Logs or via EXEC sp_readerrorlog), not to the Windows Application Event Log. After a deadlock with trace flag 1222 enabled, you can read the detail immediately with EXEC sp_readerrorlog 0, 1, 'deadlock'.
7 The Extended Events Approach: Capture and Email Deadlock Detail Advanced
The SQL Agent alert approach tells you a deadlock happened and gives you the basic error text. The Extended Events approach captures the full deadlock graph XML, which includes the exact T-SQL from both sessions, the resources they were competing for, the lock types, and which session was chosen as the victim. This is the information you need to actually fix the deadlock, not just know it occurred.
The approach has two parts: an XE session that captures deadlock events to a file, and a SQL Agent job that reads new deadlock events on a schedule and emails the details to the DBA team.
Part 1: The Extended Events Session
-- Create an Extended Events session to capture all deadlock events
-- Uses xml_deadlock_report which captures the full deadlock graph
-- Compatible with SQL Server 2016 and later
-- Adjust the file path to a directory the SQL Server service account can write to
DECLARE @xe_path NVARCHAR(260) = N'D:\XELogs\';
-- Create the directory if it does not exist (run in PowerShell first):
-- New-Item -ItemType Directory -Path 'D:\XELogs' -Force
IF EXISTS (
SELECT 1 FROM sys.server_event_sessions
WHERE name = N'Deadlock_Capture'
)
DROP EVENT SESSION [Deadlock_Capture] ON SERVER;
GO
CREATE EVENT SESSION [Deadlock_Capture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report (
ACTION (
sqlserver.database_id,
sqlserver.database_name,
sqlserver.server_instance_name
)
)
ADD TARGET package0.event_file (
SET filename = N'D:\XELogs\Deadlock_Capture.xel',
max_file_size = 100, -- MB per file
max_rollover_files = 10 -- keep last 10 files = 1 GB max
)
WITH (
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
STARTUP_STATE = ON -- session starts automatically with SQL Server
);
GO
-- Start the session immediately (it also starts on future restarts due to STARTUP_STATE = ON)
ALTER EVENT SESSION [Deadlock_Capture] ON SERVER STATE = START;
GO
-- Verify the session is running:
SELECT name, state_desc
FROM sys.dm_xe_sessions
WHERE name = N'Deadlock_Capture';Part 2: Query the Captured Deadlock Data
-- Read recent deadlock events from the XE session file
-- This query returns the timestamp, database, and full deadlock XML for each event
SELECT
xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
xdr.value('(//deadlock/process-list/process/@currentdb)[1]', 'int') AS DatabaseID,
DB_NAME(xdr.value('(//deadlock/process-list/process/@currentdb)[1]', 'int')) AS DatabaseName,
xdr.value('(//deadlock/process-list/process/@spid)[1]', 'int') AS VictimSessionID,
xdr.value('(//deadlock/process-list/process/inputbuf)[1]', 'nvarchar(max)') AS VictimQuery,
xdr.value('(//deadlock/process-list/process/inputbuf)[2]', 'nvarchar(max)') AS OtherQuery,
xdr.query('.') AS FullDeadlockXML
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = N'Deadlock_Capture'
AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDER BY DeadlockTime DESC;
-- Read from the .xel file directly (useful if the ring buffer has rolled over):
SELECT
xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
xdr.query('.') AS DeadlockXML
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
'D:\XELogs\Deadlock_Capture*.xel',
NULL, NULL, NULL
)
) AS data
CROSS APPLY event_data.nodes('//event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDER BY DeadlockTime DESC;Part 3: SQL Agent Job to Email Deadlock Details
-- Create a SQL Agent job that checks for new deadlocks every 5 minutes
-- and emails details to the DBA team when any are found
-- This gives you BOTH the alert (from the Agent alert in Section 5)
-- AND the detailed information you need to investigate
USE msdb;
GO
EXEC sp_add_job
@job_name = N'DBA - Deadlock Email Alert',
@enabled = 1,
@description = N'Reads new deadlock events from XE session and emails details to DBA team';
EXEC sp_add_jobstep
@job_name = N'DBA - Deadlock Email Alert',
@step_name = N'Read and email deadlock events',
@subsystem = N'TSQL',
@command = N'
SET NOCOUNT ON;
DECLARE @DeadlockCount INT;
DECLARE @EmailBody NVARCHAR(MAX);
DECLARE @Subject NVARCHAR(500);
-- Read deadlocks from the last 6 minutes (slightly longer than job interval)
-- to ensure no events are missed between job runs
SELECT
xdr.value(''@timestamp'', ''datetime2'') AS DeadlockTime,
DB_NAME(xdr.value(''(//deadlock/process-list/process/@currentdb)[1]'', ''int'')) AS DatabaseName,
xdr.value(''(//deadlock/process-list/process/inputbuf)[1]'', ''nvarchar(500)'') AS VictimQuery,
xdr.value(''(//deadlock/process-list/process/inputbuf)[2]'', ''nvarchar(500)'') AS OtherQuery
INTO #DeadlockEvents
FROM (
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
''D:\XELogs\Deadlock_Capture*.xel'',
NULL, NULL, NULL
)
) AS data
CROSS APPLY event_data.nodes(''//event[@name="xml_deadlock_report"]'') AS xdt(xdr)
WHERE xdr.value(''@timestamp'', ''datetime2'') >= DATEADD(MINUTE, -6, GETUTCDATE());
SELECT @DeadlockCount = COUNT(*) FROM #DeadlockEvents;
IF @DeadlockCount > 0
BEGIN
SET @Subject = N''SQL Server Deadlock Alert: ''
+ @@SERVERNAME
+ N'' | ''
+ CAST(@DeadlockCount AS NVARCHAR(10))
+ N'' deadlock(s) in last 5 minutes'';
SET @EmailBody = N''Deadlocks detected on '' + @@SERVERNAME + N''
''
+ N''''
+ N''Time (UTC) Database Victim Query Other Query '' + CONVERT(NVARCHAR(23), DeadlockTime, 126)
+ N'' '' + ISNULL(DatabaseName, ''Unknown'')
+ N'' '' + ISNULL(LEFT(VictimQuery, 300), ''N/A'')
+ N'' '' + ISNULL(LEFT(OtherQuery, 300), ''N/A'')
+ N''
''
+ N''Review the full deadlock XML in SSMS: ''
+ N''Management > Extended Events > Sessions > Deadlock_Capture
'';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N''DBAAlerts'', -- replace with your mail profile name
@recipients = N''dba@yourcompany.com'',
@subject = @Subject,
@body = @EmailBody,
@body_format = N''HTML'';
END
DROP TABLE IF EXISTS #DeadlockEvents;
',
@on_success_action = 3, -- go to next step
@on_fail_action = 2; -- quit with failure
-- Schedule: run every 5 minutes
EXEC sp_add_schedule
@schedule_name = N'Every 5 Minutes',
@freq_type = 4, -- daily
@freq_interval = 1,
@freq_subday_type = 4, -- minutes
@freq_subday_interval = 5,
@active_start_time = 0;
EXEC sp_attach_schedule
@job_name = N'DBA - Deadlock Email Alert',
@schedule_name = N'Every 5 Minutes';
EXEC sp_add_jobserver
@job_name = N'DBA - Deadlock Email Alert',
@server_name = N'(LOCAL)';
GO8 Verify Everything Is Working Beginner
Before declaring the setup complete, generate a test deadlock and confirm the full chain works. Here is a script that reliably produces a deadlock using two sessions.
Generate a Test Deadlock
-- SETUP: create two test tables (run once)
USE tempdb;
GO
CREATE TABLE dbo.DeadlockTest1 (ID INT PRIMARY KEY, Val INT);
CREATE TABLE dbo.DeadlockTest2 (ID INT PRIMARY KEY, Val INT);
INSERT INTO dbo.DeadlockTest1 VALUES (1, 100);
INSERT INTO dbo.DeadlockTest2 VALUES (1, 200);
GO
-- SESSION 1: run this first, then immediately run Session 2 before committing
BEGIN TRAN;
UPDATE tempdb.dbo.DeadlockTest1 SET Val = Val + 1 WHERE ID = 1;
-- pause here -- do not commit yet
-- now switch to Session 2 and run its code
WAITFOR DELAY '00:00:05';
UPDATE tempdb.dbo.DeadlockTest2 SET Val = Val + 1 WHERE ID = 1;
COMMIT;
-- SESSION 2: run this immediately after starting Session 1
BEGIN TRAN;
UPDATE tempdb.dbo.DeadlockTest2 SET Val = Val + 1 WHERE ID = 1;
-- Session 1 holds DeadlockTest1 and wants DeadlockTest2
-- Session 2 holds DeadlockTest2 and wants DeadlockTest1
-- SQL Server detects the cycle and kills one session with error 1205
UPDATE tempdb.dbo.DeadlockTest1 SET Val = Val + 1 WHERE ID = 1;
COMMIT;
-- CLEANUP
DROP TABLE IF EXISTS tempdb.dbo.DeadlockTest1;
DROP TABLE IF EXISTS tempdb.dbo.DeadlockTest2;Verify the Full Alert Chain
-- 1. Confirm error 1205 is now logged in the Windows Event Log
-- Check Windows Event Viewer > Windows Logs > Application
-- Source: MSSQLSERVER, Event ID: 1205
-- 2. Confirm the SQL Agent alert fired
SELECT
name,
last_occurrence_date,
last_notification_date,
occurrence_count,
last_occurrence_message
FROM msdb.dbo.sysalerts
WHERE message_id = 1205;
-- occurrence_count should increment after the test deadlock
-- 3. Confirm the XE session captured the deadlock
SELECT TOP 5
xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
xdr.query('.') AS DeadlockXML
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = N'Deadlock_Capture'
AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDER BY DeadlockTime DESC;
-- 4. Confirm trace flag 1222 wrote deadlock detail to the error log
EXEC sp_readerrorlog 0, 1, 'deadlock';
-- Should return entries with deadlock process and resource information9 The Complete Setup Script Beginner
All steps in order. Replace the four placeholder values before running: your operator name, your DBA email address, your Database Mail profile name, and your XE file path.
-- ============================================================
-- SQLYARD: Complete Deadlock Alert Setup
-- Compatible: SQL Server 2016, 2017, 2019, 2022, 2025
-- Replace: DBA Team, dba@yourcompany.com, DBAAlerts, D:\XELogs\
-- ============================================================
USE master;
GO
-- STEP 1: Enable event logging for deadlock errors
-- This is the fix for "alert never fires" -- must run before creating alerts
EXEC master.sys.sp_altermessage
@message_id = 1205,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
EXEC master.sys.sp_altermessage
@message_id = 1211,
@parameter = 'WITH_LOG',
@parameter_value = 'true';
-- Verify:
SELECT message_id, is_event_logged, text
FROM sys.messages
WHERE message_id IN (1205, 1211) AND language_id = 1033;
GO
-- STEP 2: Enable trace flag 1222 for deadlock detail in the error log
DBCC TRACEON (1222, -1);
-- STEP 3: Create or verify the DBA operator
IF NOT EXISTS (
SELECT 1 FROM msdb.dbo.sysoperators WHERE name = N'DBA Team'
)
BEGIN
EXEC msdb.dbo.sp_add_operator
@name = N'DBA Team',
@enabled = 1,
@email_address = N'dba@yourcompany.com'; -- replace
END
GO
-- STEP 4: Create deadlock alerts (drop and recreate if they already exist)
USE msdb;
GO
IF EXISTS (SELECT 1 FROM sysalerts WHERE name = N'Deadlock - Error 1205')
EXEC sp_delete_alert @name = N'Deadlock - Error 1205';
IF EXISTS (SELECT 1 FROM sysalerts WHERE name = N'Deadlock - Error 1211')
EXEC sp_delete_alert @name = N'Deadlock - Error 1211';
EXEC sp_add_alert
@name = N'Deadlock - Error 1205',
@message_id = 1205,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1;
EXEC sp_add_notification
@alert_name = N'Deadlock - Error 1205',
@operator_name = N'DBA Team',
@notification_method = 7;
EXEC sp_add_alert
@name = N'Deadlock - Error 1211',
@message_id = 1211,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1;
EXEC sp_add_notification
@alert_name = N'Deadlock - Error 1211',
@operator_name = N'DBA Team',
@notification_method = 7;
GO
-- STEP 5: Create the Extended Events capture session
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'Deadlock_Capture')
DROP EVENT SESSION [Deadlock_Capture] ON SERVER;
GO
CREATE EVENT SESSION [Deadlock_Capture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report (
ACTION (sqlserver.database_id, sqlserver.database_name, sqlserver.server_instance_name)
)
ADD TARGET package0.event_file (
SET filename = N'D:\XELogs\Deadlock_Capture.xel', -- replace path
max_file_size = 100,
max_rollover_files = 10
)
WITH (
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION [Deadlock_Capture] ON SERVER STATE = START;
GO
-- STEP 6: Verify the complete setup
SELECT 'Event Logging' AS CheckItem, CAST(is_event_logged AS VARCHAR) AS Status,
'message_id 1205' AS Detail
FROM sys.messages WHERE message_id = 1205 AND language_id = 1033
UNION ALL
SELECT 'Alert Exists', CAST(enabled AS VARCHAR), name
FROM msdb.dbo.sysalerts WHERE message_id IN (1205, 1211)
UNION ALL
SELECT 'Notification', 'OK', a.name + ' > ' + o.name
FROM msdb.dbo.sysalerts a
JOIN msdb.dbo.sysnotifications n ON n.alert_id = a.id
JOIN msdb.dbo.sysoperators o ON o.id = n.operator_id
WHERE a.message_id IN (1205, 1211)
UNION ALL
SELECT 'XE Session', state_desc, name
FROM sys.dm_xe_sessions WHERE name = N'Deadlock_Capture';
GORemember to add -T1222 as a startup parameter in SQL Server Configuration Manager to make trace flag 1222 permanent across restarts. The DBCC TRACEON command in Step 2 enables it for the current running instance only. Without the startup parameter it will be gone after the next service restart.
References
- SQLYARD: SQL Server Severity Alerts Setup
- Microsoft Tech Community: Alerting on Deadlocks with the SQL Server Management Pack
- SQLFingers: Where is the 1205 Error for SQL Server Deadlock?
- Microsoft Docs: MSSQLSERVER Error 1205
- Microsoft Docs: DBCC TRACEON Trace Flags
- Microsoft Docs: Extended Events in SQL Server
- Microsoft Docs: Deadlocks in SQL Server Transaction Locking Guide
- SQLYARD: SQL Profiler is Deprecated, Use Extended Events Instead
- SQLYARD: SQL Server Orphan Users Guide
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
