The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks<\/a>.aspx)) or being forced to start up a profiler trace are no more.<\/p>\n Let\u2019s face it. There are times when someone comes up to you and says, \u201chey we got a handful of deadlocks an hour ago can you tell me how it happened?\u201d Now, if you are practicing pro-active DBA methods then you might already have this information but if you are left with the deer in the head-lights look then these queries might be for you.<\/p>\n Extended Events was new with SQL Server 2008 and by default captures xml deadlock reports. For information about Extended Events see Pinal Dave\u2019s post here<\/a>\u00a0or you can read the white paper here<\/a>.<\/p>\n The comments on the queries below are fairly self explanatory. Running the first query will return a single record with a lot of improperly formatted xml. This query is given because it is very fast. All you need to do then is paste it into notepad and search for xml_deadlock_report to find the deadlock graph xml. I provided the bottom query for use when you come across the sql handles in the xml to identify the queries that were being run.<\/p>\n The middle query is the best of the bunch here but can be very slow. This one will return a row per deadlock graph in properly formatted xml. Must easier to work with.<\/p>\n In both cases the most recent deadlock events will be at the bottom of the result set or the bottom of the xml code block copied out from query one.<\/p>\n -- need to paste the result in notepad and search for xml_deadlock_report.<\/p>\n select CAST(target_data as xml) as TargetData<\/p>\n from sys.dm_xe_session_targets st<\/p>\n join sys.dm_xe_sessions s on s.address = st.event_session_address<\/p>\n where name = 'system_health'<\/code><\/p>\n -- but this query is VERY slow. There is no server performance impact<\/p>\n -- but you should expect this query to run for 30 mins +<\/p>\n select XEventData.XEvent.value('(data\/value)[1]', 'varchar(max)') as DeadlockGraph<\/p>\n FROM<\/p>\n (select CAST(target_data as xml) as TargetData<\/p>\n from sys.dm_xe_session_targets st<\/p>\n join sys.dm_xe_sessions s on s.address = st.event_session_address<\/p>\n where name = 'system_health') AS Data<\/p>\n CROSS APPLY TargetData.nodes ('\/\/RingBufferTarget\/event') AS XEventData (XEvent)<\/p>\n where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'<\/code><\/p>\n SELECT * FROM sys.dm_exec_sql_text(0x03001a00ea93ed5bed752b0150a100000100000000000000);<\/code><\/p>\n Credits:<\/strong> (edited \u2013 2013-02-13)<\/p>\n Many thanks to\u00a0Jonathan Kehayias<\/a>\u00a0for authoring these scripts and providing an informative article here <\/a>on sqlservercentral.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Background The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks.aspx)) or being forced to start up a profiler trace are no more. Let\u2019s face it. There are times when someone comes up to you and says, \u201chey we got a handful of deadlocks an hour ago can […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-49","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/49","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=49"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/49\/revisions"}],"predecessor-version":[{"id":312,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/49\/revisions\/312"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=49"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=49"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=49"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}The Code<\/h1>\n
-- This query is fast but the xml is not formatted properly so you<\/p>\n-- This query gives you a record per deadlock graph with xml corrected<\/p>\n\/For viewing the sql handles<\/em>\/<\/p>\n