1. SQL Server Extended Events (Recommended)

Extended Events provide a lightweight and powerful way to capture deadlock information.

• Open SQL Server Management Studio (SSMS).
• Go to Management > Extended Events > Sessions.
• Right-click and choose New Session Wizard.
• Add the xml_deadlock_report event.
• Save the session and start it.

This captures XML deadlock graphs that show the processes and resources involved.

2. SQL Server Profiler (Legacy Tool)

Profiler can capture deadlocks using the Deadlock Graph event.

• Start a new trace.
• Include the Deadlock Graph, Lock:Deadlock, and Lock:Deadlock Chain events.
• Run the trace and wait for a deadlock to occur.

Profiler is more resource-intensive and not recommended for production use.

3. System Health Extended Event (on by default)

SQL Server automatically logs deadlocks in the system_health session.

Query to extract deadlocks:

SELECT xed.value('@timestamp', 'datetime2') AS deadlock_time_utc,
       xed.query('.') AS deadlock_graph
FROM (SELECT CAST(t.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'system_health'
        AND t.target_name = N'ring_buffer') AS d
CROSS APPLY d.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed)
ORDER BY deadlock_time_utc DESC;

The ring buffer only keeps recent events. Older deadlocks are in the system_health .xel files in the instance’s LOG folder.

4. SQL Server Error Log

Deadlocks are sometimes logged in the SQL Server Error Log, especially if trace flag 1222 or 1204 is enabled. Enable trace flag 1222:

DBCC TRACEON (1222, -1);   -- on until the next restart
-- To keep it on permanently, add -T1222 to the start-up parameters.

This logs detailed deadlock information in a readable format.

5. Third-Party Monitoring Tools

• Redgate SQL Monitor
• SolarWinds DPA
• SolarWinds SQL Sentry (formerly SentryOne).

From my SQL Server DBA handbook.