Retrieving Historical Deadlock Graphs
When I visit a client site to troubleshoot deadlocks, it is
always a cause of annoyance when I cannot go through historical deadlock events to determine
the frequency and causes of these deadlocks. I either have to talk them
through enabling trace flag 1222 or explain to them how to enable profiler to
capture specific deadlock graphs. Even then its not possible to retrospecivley go back
and examine deadlocks before this point.
I recently came accross this article by Jonathan Kehayias about how to retrieve
historical deadlock graphs with 2008 extended
events. These scripts have proved invaluable for me in going back
in time.
You may need to upgrade to Service Pack 2 to
get it to work correctly, but heres a copy of the
final script:
declare
@xml xml
select
@xml =
target_data
from
sys.dm_xe_session_targets
join
sys.dm_xe_sessions on
event_session_address = address
where
name = 'system_health'
select
CAST(
REPLACE
(
REPLACE
(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>'
, '<deadlock><victim-list>'),
'<process-list>'
,'</victim-list><process-list>')
as
xml) as
DeadlockGraph
FROM
(
select @xml as
TargetData) AS Data
CROSS
APPLY TargetData.nodes
('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData (XEvent)