SQL Server records deadlock graphs as xml_deadlock_report events in the built-in system_health Extended Events session. On Amazon RDS, query that session from T-SQL and extract the process attributes you need instead of depending on access to the Windows file system.
Last updated: October 22, 2026.
WITH target AS (
SELECT CAST(st.target_data AS xml) AS target_xml
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS st
ON st.event_session_address = s.address
WHERE s.name = N'system_health'
AND st.target_name = N'ring_buffer'
), deadlocks AS (
SELECT event_node.query('.') AS event_xml
FROM target
CROSS APPLY target_xml.nodes(
'//RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS events(event_node)
)
SELECT
process_node.value('@spid', 'int') AS spid,
process_node.value('@hostname', 'nvarchar(256)') AS host_name,
process_node.value('@loginname', 'nvarchar(256)') AS login_name,
process_node.value('@clientapp', 'nvarchar(256)') AS client_app,
process_node.value('@waitresource', 'nvarchar(512)') AS wait_resource,
process_node.value('(inputbuf/text())[1]', 'nvarchar(max)') AS input_buffer
FROM deadlocks
CROSS APPLY event_xml.nodes(
'/event/data/value/deadlock/process-list/process'
) AS processes(process_node);The ring buffer is convenient for recent incidents, but it has limited retention. Save the original deadlock XML before simplifying it to rows because the graph contains victim, resource, lock-mode, and execution-stack details that a short table cannot preserve.
Use the graph to identify both sides
The Microsoft deadlocks guide confirms that system_health captures deadlock reports by default. Start with the victim list, then match process IDs to the resource list. Compare statements, transaction age, lock modes, isolation levels, and access order; the victim query is not automatically the query that caused the design problem.
Common remedies include making transactions shorter, accessing tables in a consistent order, adding a selective index, or reducing the number of rows touched. Do not treat automatic retries as the only fix. Retries reduce user-visible failures, but a recurring deadlock still consumes work and can indicate an avoidable concurrency pattern.
Retain events beyond the ring buffer
For longer retention, create a separate Extended Events session with an event-file target. The AWS RDS Extended Events guide requires event files under D:\rdsdbdata\log, and SQL Server can read them through sys.fn_xe_file_target_read_file(). Store only what your incident process needs and protect captured SQL text.
Correlate the UTC event timestamp with application logs and a request or job identifier. Then reproduce the transaction ordering in a test environment before changing indexes or isolation behavior. Continue with finding Query Store plans by index, diagnosing a slow first write, and understanding SQL Server session timeouts.
