Saturday, July 11, 2009

 

Retrieve Deadlock Info with SQL Server 2008

Did you know that SQL Server 2008 has the ability to retrieve deadlock information after the fact without having previously enabled any additional tracing?  It’s part of the new advanced troubleshooting feature called Extended Events. Here’s a SQL query from Jonathan Kehayias which retrieves SQL Server 2008 deadlock information:

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 CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'system_health') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

More info. here: Using SQL Server 2008 Extended Events


# posted by Mitch Wheat @ 6:59 pm

0 Comments:

Post a Comment



<< Home
    

Powered by Blogger