Deadlocks are one of those SQL Server problems that can quietly destroy application performance and user experience before anyone realises what's happening. Two transactions block each other waiting for resources the other holds, SQL Server picks a victim and rolls one back, and your application throws an error. If you're not monitoring for deadlock conditions, you're flying blind.

The good news is that SQL Server gives you several reliable methods to detect and alert on deadlocks. The four main approaches are trace flags, Extended Events, SQL Server Profiler, and SQL Server Agent alerts. This article covers the practical setup for each, with working scripts you can deploy today.

Why Deadlock Monitoring Matters

A single deadlock is usually not a crisis. Recurring deadlocks are a serious problem. They indicate contention in your application's data access patterns, often caused by inconsistent transaction ordering, missing indexes, or overly broad locking. Without monitoring in place, your DBA team is relying on users to report errors, which means you're always reacting after the fact.

Proactive deadlock detection lets you capture the full deadlock graph, identify the victim transaction, see which queries and tables are involved, and fix the root cause before it escalates. The earlier you catch a pattern, the cheaper it is to resolve.

Method 1: Trace Flags 1204 and 1222

Trace flags are the simplest way to start capturing deadlock information. When enabled, trace flags 1204 and 1222 write deadlock details directly to the SQL Server error log every time a deadlock occurs.

  • Trace flag 1204 returns information about the nodes involved in the deadlock, formatted in a simpler text output.
  • Trace flag 1222 returns more detailed deadlock information including the full XML-style deadlock graph. This is the preferred flag for SQL Server 2005 and later.

Running both together gives you maximum visibility. Enable them globally with this command:

DBCC TRACEON (1204, 1222, -1);
GO

The -1 parameter applies the flags at the global level rather than just the current session. To confirm the flags are active:

DBCC TRACESTATUS;
GO

These flags are not persistent across service restarts. To make them permanent, add -T1204 and -T1222 as startup parameters for your SQL Server instance. Do this through SQL Server Configuration Manager, under the instance's properties and startup parameters tab. Refer to the Microsoft documentation on DBCC TRACEON for the full parameter reference.

One limitation worth knowing: the error log can grow quickly on a busy server with frequent deadlocks. Make sure your error log cycling is configured appropriately, or you'll end up with very large log files that are difficult to parse.

Method 2: Extended Events

Extended Events is the recommended approach for deadlock monitoring on SQL Server 2012 and later. It's more lightweight than Profiler, captures the full deadlock graph in XML format, and stores results in a queryable target file rather than flooding the error log.

SQL Server 2012 introduced a GUI for Extended Events in SQL Server Management Studio, which made setup significantly more accessible. A system health session runs by default on all SQL Server instances from 2008 onwards and already captures deadlock information. You can query it directly:

SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_timestamp,
    xdr.query('.') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'system_health'
    AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_timestamp DESC;

This query extracts deadlock events from the default system health ring buffer. It's a quick way to review recent deadlocks without setting up anything additional. The ring buffer only retains a limited amount of data, so for persistent capture you'll want to set up a dedicated Extended Events session that writes to a file target.

For SQL Server 2008 and 2008 R2, Extended Events were available but required scripted configuration only. The GUI wasn't introduced until 2012. If you're still running older instances, the trace flag approach is more practical.

Method 3: SQL Server Agent Alerts

SQL Server Agent alerts let you receive immediate email notifications when a deadlock occurs. This is particularly valuable for production environments where the operations team needs to know about deadlock conditions in real time rather than discovering them during a log review.

Before configuring alerts, make sure Database Mail is configured and your SQL Server Agent is set up with an operator and notification profile. Without that foundation, the alerts will fire but no one will receive them.

The following script creates an alert that fires on error message 1205, which is the deadlock victim error:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert
    @name = N'General: Deadlock Event',
    @message_id = 1205,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 5,
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO

The @delay_between_responses = 60 parameter means the alert will wait at least 60 seconds before firing again, even if multiple deadlocks occur in quick succession. On a server with a deadlock storm, this prevents your inbox from being flooded. Adjust this value based on your environment's tolerance.

The following script creates an alert for lock allocation failures (error 17125):

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert
    @name = N'General: Lock Allocation',
    @message_id = 17125,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 5,
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000'
GO

After running these scripts, open each alert in SQL Server Management Studio under SQL Server Agent, then Alerts. Edit each one to add a notification response pointing to your operator. The scripts create the alert conditions, but the notification target needs to be configured manually or added to the script with an additional sp_add_notification call.

Method 4: SQL Server Profiler

Profiler is the oldest approach and the one most DBAs are familiar with. You can capture deadlock graph events by adding the Deadlock graph, Lock:Deadlock, and Lock:Deadlock Chain events to a trace. The graphical deadlock output in Profiler is actually quite readable and useful for one-off investigations.

That said, Profiler has a real overhead cost on busy servers. Microsoft has been steering DBAs toward Extended Events for years, and Profiler is not recommended for persistent production monitoring. Use it for targeted short-term investigations, not as your primary deadlock detection mechanism.

Which Method Should You Use?

For most production environments, the right answer is a combination of approaches:

  1. Enable trace flags 1204 and 1222 as startup parameters for a persistent error log record.
  2. Query the system health Extended Events session for recent deadlock graphs.
  3. Configure SQL Server Agent alerts on message 1205 so your team gets immediate notification.

This gives you real-time alerting, historical records in the error log, and detailed XML deadlock graphs for root cause analysis. Extended Events with a file target is worth adding if deadlocks are a recurring problem and you need longer retention of the full graph data.

Key Takeaways

  • Trace flags 1204 and 1222 write deadlock details to the SQL Server error log and should be added as startup parameters to persist across restarts.
  • The default system health Extended Events session already captures deadlock graphs on SQL Server 2008 and later. You can query it immediately without any additional setup.
  • SQL Server Agent alert on message ID 1205 provides real-time notification of deadlock victims. Pair it with a configured Database Mail operator for it to be useful.
  • Profiler is suitable for short-term investigation but carries overhead. Don't use it for persistent production monitoring.
  • Recurring deadlocks point to a root cause in application query patterns or indexing. Detection is the first step, but resolution requires analysing the deadlock graph to identify the conflicting transactions.

If deadlocks are a recurring issue in your environment, a structured review of your locking and indexing strategy will typically resolve the underlying problem. DBA Services provides SQL Server health checks and managed support that include deadlock analysis, wait statistics review, and query optimisation. Contact us to find out how we can help stabilise your SQL Server environment.