InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:15:47 GMT20http://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxAny time I am running any of the suggested code to grab the deadlock graphs, I am looking at the xml and seeing that object_id in the resource-list is blank. Is it supposed to be this way? The reason I ask is because I found another snippet of code that parses the graph out to log the essential information to a table.1. Script to produce graph:[code="sql"]select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraphFROM (select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'OPTION(MAXDOP 1)[/code] I use the MAXDOP because parallelism makes my execution time huge the first time I run this.2. Add in my parsing script:[code="sql"];WITH log_deadlocksAS ( select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) 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' )Select --DeadlockDateTime, DeadlockGraph, DbName = DB_NAME(ResList.KeyLock.value('@dbid', 'INT')), ObjectName = ResList.KeyLock.value('@objectname', 'sysname'), IndexName = ResList.KeyLock.value('@indexname', 'sysname'), ResultingLockMode = ResList.KeyLock.value('@mode', 'varchar(10)'), OwnerID = OwnList.Own.value('@id', 'sysname'), OwnerLockMode = OwnList.Own.value('@mode', 'varchar(10)'), WaiterID = WaitList.Wait.value('@id', 'sysname'), WaiterLockMode = WaitList.Wait.value('@mode', 'varchar(10)')From Log_Deadlocks-- Shred keylock node of the resource-listCross Apply DeadlockGraph.nodes('//resource-list/keylock') ResList(KeyLock)-- Shred the owner node beneath the resource-list/keylock nodeCross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own)-- Shred the waiter node beneath the resource-list/keylock nodeCross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);[/code]When I look at Jonathan's Extended Event Session Explorer (We are on 2008 R2) I DO see object names for the deadlocks. Any thoughts?Wed, 27 Feb 2013 09:28:54 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxIf you are able to implement the code I attached you will get a picture representation emailed to you and a XML representation logged to a table.Tue, 18 Sep 2012 13:56:20 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxToday we had an instance of deadlock and I was able to see that using the below code..[center][code="sql"]select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraphFROM (select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'[/code][/center]And the output:[code="xml"]<deadlock> <victim-list> <victimProcess id="process6430748" /> </victim-list> <process-list> <process id="process6430748" taskpriority="0" logused="0" waitresource="PAGE: 17:22:8689" waittime="2293" ownerId="2733945332" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:34.633" XDES="0x29f702a900" lockMode="S" schedulerid="13" kpid="2916" status="suspended" spid="137" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:34.533" lastbatchcompleted="2012-09-18T09:37:34.533" clientapp="cogent.asset" hostname="-1:war-cog-tml-01v.asset.local" hostpid="2584" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733945332" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568"> <executionStack> <frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" /> </executionStack> <inputbuf>Proc [Database Id = 17 Object Id = 1051709578] </inputbuf> </process> <process id="process7c33288" taskpriority="0" logused="27340" waitresource="PAGE: 17:22:8434" waittime="2623" ownerId="2733257730" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:13.997" XDES="0x39e9f11950" lockMode="IX" schedulerid="11" kpid="12084" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:13.890" lastbatchcompleted="2012-09-18T09:37:13.887" lastattention="2012-09-18T05:37:50.263" clientapp="cogent.asset" hostname="-1:WAR-COG-TML-03V.asset.local" hostpid="3740" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733257730" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568"> <executionStack> <frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" /> </executionStack> <inputbuf>Proc [Database Id = 17 Object Id = 1051709578] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="22" pageid="8689" dbid="17" objectname="" id="lock3676adf200" mode="IX" associatedObjectId="72057607072907264"> <owner-list> <owner id="process7c33288" mode="IX" /> </owner-list> <waiter-list> <waiter id="process6430748" mode="S" requestType="wait" /> </waiter-list> </pagelock> <pagelock fileid="22" pageid="8434" dbid="17" objectname="" id="lock36772e3700" mode="S" associatedObjectId="72057607072907264"> <owner-list> <owner id="process6430748" mode="S" /> </owner-list> <waiter-list> <waiter id="process7c33288" mode="IX" requestType="wait" /> </waiter-list> </pagelock> </resource-list></deadlock>[/code]It was case the same SP is being called by different App/User and deadlocking in somewhere. Its a complex SP, we are investigating to optimize the code.This is awesome! without having to setup anything. I wish we had pictorial representation as well for presentation :)...We are having multiple deadlock issues on different jobs/Procs, so will it be able to show all that occurrence or just the most recent one?ThanksTue, 18 Sep 2012 13:41:37 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]john.m.couch (9/17/2012)[/b][hr]Did you look at the script I provided? IT has everything you need to do scripted in it.[/quote]Thanks for providing the script.I have limited access to production env, hence could not use it. But have passed it to our DBA for review and implementation.Tue, 18 Sep 2012 13:21:57 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxHey John,No worries, I just haven't had the time to go look at the code with teaching at Redmond this week.Tue, 18 Sep 2012 00:34:17 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxYes Jonathon, it is from the code you provided at Pass. It persists it by sending an email notification and writing it out to a table. Either way. On a side note, it is documented in the script (in the procedure comment section) that the code was pulled from your pass summit files and provides a link to where you had them posted.Mon, 17 Sep 2012 20:10:24 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]hello mahesh (9/17/2012)[/b][hr][quote][b]Jonathan Kehayias (9/17/2012)[/b][hr][quote][b]hello mahesh (9/17/2012)[/b][hr]Still noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.May be it got overlapped as the deadlock event occured couple of weeks back.Thanks[/quote]If you restarted the instance since the deadlock occurred it won't be in the system_health event session since it is a memory resident target and not persisted across service failures/restarts.[/quote]The system was not restarted; but it is a very active OLTP system so I m guessing the event might got flushed out. Whats the amount of time or data that it hold in the queue?The deadlocks are occurring in batch jobs and is a repeated phenomenon in the system, though for some reason it has not occurred in last two weeks. Do I need to set/check any settings in the server to capture deadlock?Thanks[/quote]If it's not in the session and the server has been restarted, you are either:A: on RTM version and not collecting the graph from a bug that was fixed and should apply the latest SPB: having tons of other resource contention issues in the instance that you should look atYou can skip all of the Extended Events stuff if you use Event Notifications, which is what John sent you the code to do. I haven't looked at his solution, but if it is based on what I did at PASS I am guessing that he just added to the solution and it should allow you to persist any deadlock to a table for later review.Mon, 17 Sep 2012 19:52:51 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxDid you look at the script I provided? IT has everything you need to do scripted in it.Mon, 17 Sep 2012 18:48:58 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]Jonathan Kehayias (9/17/2012)[/b][hr][quote][b]hello mahesh (9/17/2012)[/b][hr]Still noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.May be it got overlapped as the deadlock event occured couple of weeks back.Thanks[/quote]If you restarted the instance since the deadlock occurred it won't be in the system_health event session since it is a memory resident target and not persisted across service failures/restarts.[/quote]The system was not restarted; but it is a very active OLTP system so I m guessing the event might got flushed out. Whats the amount of time or data that it hold in the queue?The deadlocks are occurring in batch jobs and is a repeated phenomenon in the system, though for some reason it has not occurred in last two weeks. Do I need to set/check any settings in the server to capture deadlock?ThanksMon, 17 Sep 2012 18:15:03 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]hello mahesh (9/17/2012)[/b][hr]Still noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.May be it got overlapped as the deadlock event occured couple of weeks back.Thanks[/quote]If you restarted the instance since the deadlock occurred it won't be in the system_health event session since it is a memory resident target and not persisted across service failures/restarts.Mon, 17 Sep 2012 16:35:22 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxAttached is the script I created using Jonathon's provided code. If you replace all the values I have marked with <xxx in the script and run it, it will setup the deadlock queue for you. It works on all my 2008 and 2008 R2 environments. Change the extension to .sqlMon, 17 Sep 2012 16:20:03 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxStill noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.May be it got overlapped as the deadlock event occured couple of weeks back.ThanksMon, 17 Sep 2012 16:11:00 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]hello mahesh (9/17/2012)[/b][hr]We had couple of deadlock issues,but nothing is captured with this query..[/quote]First try this:[code]WITH SessionData as ( SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') VarcharResults 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[@name="xml_deadlock_report"]') AS XEventData (XEvent) )SELECT substring(VarcharResults, charindex( 'lasttranstarted="', VarcharResults) + len('lasttranstarted="'),19) ApproxDateGuess, cast ( REPLACE(REPLACE( case -- bad tag, should be <victimProcess id="process_______"/> when patindex( '%<victimProcess id="process_______">%', VarcharResults ) > 1 then stuff( VarcharResults , patindex( '%<victimProcess id="process_______">%', VarcharResults )+34,0,'/') else VarcharResults end , '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') as XML ) DeadlockTreeFROM SessionDataORDER BY 1;[/code]if that doesn't work, I can think of two things--1) If you have a lot of extended events, the ring buffer may have lapped itself2) I've seen cases where it appears like an event with bad XML gets stuck in the ring buffer and you can't see past it until the bad event gets over written.Mon, 17 Sep 2012 16:00:30 GMTdouglarhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxWe had couple of deadlock issues,but nothing is captured with this query..select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraphFROM(select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'Mon, 17 Sep 2012 15:11:21 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxWhat requirement? For the deadlock notification? Its the same as it is on reg 2008.Mon, 17 Sep 2012 13:35:04 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]Jonathan Kehayias (2/20/2009)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/articles/deadlocks/65658/">Retrieving Deadlock Graphs with SQL Server 2008 Extended Events</A>[/B][/quote]Whats the requirement for it to work on 2008 R2 version?select @@VERSIONMicrosoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)Mon, 17 Sep 2012 13:05:21 GMThello maheshhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI've tried every version of the code pasted on the page and consistently get this error:Msg 9436, Level 16, State 1, Line 1XML parsing: line 5, character 15, end tag does not match start tagFri, 15 Jun 2012 12:00:58 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxOK, dumb question, does the deadlock notification event you created only work on 1 database? Or does it apply to the whole server, and deadlock that occurs?Wed, 11 Apr 2012 20:58:43 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxOK, so, I used your code, and noted that in the code I have written that it was taken from your code provided in the PASS Summit, and I setup a Notification Event as you did. I have a couple of questions:1. Used some logic found here, http://forums.overclockers.com.au/showthread.php?t=689566, thinking maybe I could attach the XML output to the email vs. just using the text version in the body??? Haven't seen a deadlock come through yet, but will shortly, without a doubt.On a side note, thanks for all your guidance and information. Appreciate it.Wed, 11 Apr 2012 14:59:54 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI don't have any trace flags enabled. I am simply having this triggered from a 1205 alert, going and grabbing the last 1205 error that hit the log, and pulling the deadlock report for it.Speaking of the Event Notification method, I was reading through your sample, looking at the code, it appears I could drop the use of the table and just send the email message as I am doing now. Also, the only reason I pursued the method I did was to learn from it, and how to get what I was looking for. It is not an end all solution, merely a learning tool. I have to dig into things and play around to learn.Tue, 10 Apr 2012 21:40:30 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]john.m.couch (4/10/2012)[/b][hr]Jonathon,I actually got it to work. I am attaching my code. Not the prettiest, but it works.[/quote]This is incredibly inefficient when compared to the very simple implementation using Event Notifications from my last referenced link. Sometimes just because you can, doesn't necessarily mean that you should. The fact that you have to have a trace flag enabled, and then have to read the ERRORLOG file to do something that is natively possible using Event Notifications with neither of these steps makes this not make sense as a full solution.Tue, 10 Apr 2012 21:28:09 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxJonathon,I actually got it to work. I am attaching my code. Not the prettiest, but it works.Tue, 10 Apr 2012 21:10:53 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]john.m.couch (4/9/2012)[/b][hr]I was under the impression there was a performance overhead with WMI Alerts. And I was really just looking for the textual output provided to send in the email. I also don't want to push the data to a table, I just wanted to email out the text and move on. I'll keep working on it.[/quote]WMI Alerts, as well as your Error Severity Alerts, piggy back on Event Notifications through msdb, so you are leveraging the same underlying mechanism either way. You can forgo all of that by just using an Event Notification as well. There is a full setup script in my PASS presentation demos from 2011 that you could use as well.[url=http://www.sqlskills.com/2011PastConferences.asp] SQLskills Resource Page 2011 Conferences [/url]Mon, 09 Apr 2012 17:32:43 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI was under the impression there was a performance overhead with WMI Alerts. And I was really just looking for the textual output provided to send in the email. I also don't want to push the data to a table, I just wanted to email out the text and move on. I'll keep working on it.Mon, 09 Apr 2012 17:22:33 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]john.m.couch (4/9/2012)[/b][hr]Jonathon - I took/adjusted your code along with someone else's modified version of your code, and have gotten stuck trying to figure out why it sometimes retrieves and sometimes doesn't. I was basically trying to pass in the Message Text from the 1205 alert to a Job kicked off from the alert, have the procedure strip the SPID from the text, and retrieve the deadlock report and email it to me. This is the code I was using. I The SPID / Date combination were essentially supposed to be adjusted to be 5-10 minutes ago, and the SPID from the 1205 error. I was creating a deadlock, then attempting to pull the report. It worked 1 time, then never again.[/quote]If you are triggering this with a SQL Agent Alert, you should just flip that over to a WMI alert as shown in the books online, and then you have tokenized access to the graph from inside of the Job that responds to the alert. No point going back to Extended Events if you are trying to do alerting off SQL Agent Alerts.Mon, 09 Apr 2012 16:55:11 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxJonathon - I took/adjusted your code along with someone else's modified version of your code, and have gotten stuck trying to figure out why it sometimes retrieves and sometimes doesn't. I was basically trying to pass in the Message Text from the 1205 alert to a Job kicked off from the alert, have the procedure strip the SPID from the text, and retrieve the deadlock report and email it to me. This is the code I was using. I The SPID / Date combination were essentially supposed to be adjusted to be 5-10 minutes ago, and the SPID from the 1205 error. I was creating a deadlock, then attempting to pull the report. It worked 1 time, then never again. declare @ldt_StartDate datetime2 = dateadd(mi, -10, getdate()) ,@li_ProcessID int = 1438 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_sessions as s inner join sys.dm_xe_session_targets as t on s.address = t.event_session_address where s.name = 'system_health' and t.target_name = 'ring_buffer' ) as i cross apply TargetData.nodes ('//RingBufferTarget/event') as xEventData(xEvent) where xEventData.xEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' --and xEventData.xEvent.value('@timestamp', 'datetime') >= @ldt_StartDate --and xEventData.xEvent.value('(data/value)[1]','varchar(max)') like '%spid="' + cast(@li_ProcessID as varchar) + '%'Mon, 09 Apr 2012 16:14:31 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxYou'd have to write a process that query's the system_health session for graphs and then emails you them. There is no way to do it otherwise.Thu, 05 Apr 2012 06:41:55 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxHow do you actually use the extended event to capture the graph and statement and email it for troubleshooting?Thu, 05 Apr 2012 06:37:36 GMTjohn.m.couchhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxBriljant article :-)Pinned your name on my "must read" list !Mon, 12 Mar 2012 03:44:23 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI'm a little late getting into Extended Events but the code provided by Jonathan is great. I needed to trace some deadlock events on a SQL 2008 SP1 server and need to alter the replace logic a little to deal with a missing / in the process victim list. Here it is, hopefully it helps someone else:[code="sql"]select @@VERSIONdeclare @xml xmlselect @xml = target_datafrom sys.dm_xe_session_targetsjoin sys.dm_xe_sessions on event_session_address = addresswhere 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 DeadlockGraphFROM(select @xml as TargetData) AS DataCROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)[/code]Fri, 27 Jan 2012 08:36:45 GMTFibRockhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxNo, but then again I wouldn't use Extended Events for this type of task, I would instead use Event Notifications as shown in my PASS 2011 demos:[url]http://www.sqlskills.com/resources/conferences/300_UsingEventNotificationsSQLServer20052008.zip[/url]Cheers,Mon, 09 Jan 2012 00:23:41 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxHi Jonathan,Do you have pointers to create a SQL job that sends deadlock notifications , for sql server 2008 by using the extended events?Thanks.Sun, 08 Jan 2012 22:52:46 GMTForumNewbiehttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxGreat article. During our morning meeting my boss commented that he saw a deadlock on a high profile server that goes live Friday and couldn't find any information on it. By the end of the meeting (about 10 minutes) I had found this article, and was able to give him both queries and both SPIDs so we could start tracking down what happend. Very cool!ThanksKennethMon, 05 Dec 2011 09:35:15 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxJon, thanks for your quick response and clarifying things.CheersMuraliFri, 04 Nov 2011 07:42:18 GMTmurali.jillellamudi 85871http://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspx[quote][b]murali.jillellamudi 85871 (11/4/2011)[/b][hr]I could get the output but I could not open the xml as xdl file to get a graphicla view. Your help is much appreciated. RegardsMurali[/quote]This is covered in the article at the end.[quote][i]From here, you can click on each XML graph and have it open expanded as a XML document in Management Studio. However, unlike the deadlock graph that is generated by SQL Trace, you can't save this XML document as a .XDL file and open it graphically in Management Studio. The reason for this is because the Extended Events Deadlock Graph is a new XML schema and format that differs from the one that is being output by SQL Trace and the Trace Flags for backwards compatibility. The new deadlock graph output will display multiple victims which was a short coming in the old format, so it is necessary to actually be able to read the deadlock graph if you plan to use the information generated by Extended Events for troubleshooting deadlocks.[/i][/quote]Fri, 04 Nov 2011 07:01:01 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxHello Guys, I am trying to run one of the scripts to capture the Deadlock info. I could not get a working version . select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')as xml) as DeadlockGraphFROM(select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent) [highlight=#ffff11][b]I am on SQL SERVER 2008 R2 CU5. [/b][/highlight]I could get the output but I could not open the xml as xdl file to get a graphicla view. Your help is much appreciated. RegardsMuraliFri, 04 Nov 2011 06:34:33 GMTmurali.jillellamudi 85871http://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI get the bad XML error because of tags like this that show up intermittently--<victim-list> [b]<victimProcess id="process541c988">[/b] <victimProcess id="process6e8bdc8"/> </victim-list>I've started doing this--[code]WITH SessionData as ( SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') VarcharResults 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[@name="xml_deadlock_report"]') AS XEventData (XEvent) )SELECT substring(VarcharResults, charindex( 'lasttranstarted="', VarcharResults) + len('lasttranstarted="'),19) ApproxDateGuess, cast ( REPLACE(REPLACE( case -- bad tag, should be <victimProcess id="process_______"/> when patindex( '%<victimProcess id="process_______">%', VarcharResults ) > 1 then stuff( VarcharResults , patindex( '%<victimProcess id="process_______">%', VarcharResults )+34,0,'/') else VarcharResults end , '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') as XML ) DeadlockTreeFROM SessionDataORDER BY 1;[/code]But it seems that I don't get the most recent few weeks of data. I know I've gotten deadlocks in the past two weeks, but when I run it today, the most recent record is from 5-6-2011Here's my @@version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)Mon, 23 May 2011 08:12:48 GMTdouglarhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxHi,Remember there was a bug in the XML structure returned and the REPLACE commands are there to fix it. Given microsoft has already filed it and you are on the latest build of SQL2008 R2 no need to have REPLACE commans. So tryselect CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')as xml) as DeadlockGraphFROM(select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)Sat, 12 Feb 2011 10:22:59 GMTrama.mathanmohanhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxI copied below code and it is giving me the xml parsing error, note that I am running it on SQL [b]Server 2008 R2, Patch level 1753.[/b]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 DeadlockGraphFROM(select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets stjoin sys.dm_xe_sessions s on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)Fri, 11 Feb 2011 14:49:40 GMTRaxhttp://www.sqlservercentral.com/Forums/Topic661084-1365-1.aspxgreat article, extremely helpful. Rather new to SQL 2008, and I was only used to looking for things in the logs in SQL 2000... so this was a revelation. The XML files are perfect to see which query(ies) are causing the deadlock.Tue, 23 Nov 2010 12:25:10 GMTnasi