Parsing Extended Events xml_deadlock_report

re: Parsing Extended Events xml_deadlock_report

Jonathan Kehayias — Mon, 10 May 2010 02:16:25 GMT

Michael,

What is interesting is that you report that an intraquery parallelism deadlock isn't captured by TF 1222, which is contrary to my own experiences recently with that type of deadlock as well as experiences in the past. I know for a fact that in SQL 2005 1222 will output for intraquery parallelism deadlocks, but I have yet to have a problem with them in SQL 2008, mainly because I have changed how I configure my servers for 'max degree of parallelism'. If the TF 1222 didn't capture the deadlock I'd call that a bug and something that should be fixed. The good thing is that the deadlock XML bug in XEvents has been fixed and will be released in Service Pack 2, per the comments on the connect bug which has been closed as fixed.

re: Parsing Extended Events xml_deadlock_report

Michael Zilberstein — Mon, 10 May 2010 04:18:30 GMT

Jonathan,

Agree with every word - I know that TF 1222 used to catch intra-query parallelism deadlocks, have seen it multiple times on my systems. I'm actually considering opening bug on connect - I wrote my post at ~4:00AM local time, so postponed it to get a little nap :-).

re: Parsing Extended Events xml_deadlock_report

Bill — Mon, 17 May 2010 22:42:57 GMT

I thought SQL 2008 CU1 package fixed that deadlock xml bug.

Does SQL 2008 SP1 include the fix for that?

An XEvent a Day (13 of 31) – The system_health Session

The Rambling DBA: Jonathan Kehayias — Tue, 14 Dec 2010 03:13:58 GMT

Today’s post was originally planned for this coming weekend, but seems I’ve caught whatever bug my kids

re: Parsing Extended Events xml_deadlock_report

Scotty Boy — Thu, 20 Oct 2011 21:11:18 GMT

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 5, character 15, end tag does not match start tag

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

looks to me like the XML file is truncated prematurely

re: Parsing Extended Events xml_deadlock_report

mark — Tue, 24 Apr 2012 10:25:11 GMT

I also get this

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

any ideas?

re: Parsing Extended Events xml_deadlock_report

Eric Zierdt — Thu, 31 May 2012 17:51:35 GMT

I've just noticed that when I have multiple victimProcess nodes under the victim-list node, that the first one doesn't terminate (no /> at the end, just >) This causes the XML parsing: line 6, character 15, end tag does not match start tag error that people are reporting. Not sure yet if there is a way to catch this and correct it..i'm still working on it...I'm not seeing anyone else discussing this anywhere.

<deadlock-list> <deadlock><victim-list> <victimProcess id="processb8034c8"> <victimProcess id="process753c508"/> </victim-list><process-list>

...

re: Parsing Extended Events xml_deadlock_report

Robert Berndt — Thu, 28 Jun 2012 11:25:00 GMT

I also get this

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

Would love a solution!

re: Parsing Extended Events xml_deadlock_report

Igor Santos — Thu, 06 Sep 2012 15:31:02 GMT

I'm having the same issue that Eric Zierdt and Robert Berndt are experiencing.

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

re: Parsing Extended Events xml_deadlock_report

M. Duverger — Thu, 27 Sep 2012 13:12:34 GMT

Hi guys,

I'm having the same issue that Eric Zierdt, Robert Berndt and Igor Santos are experiencing.

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

Is there any solution yet?

re: Parsing Extended Events xml_deadlock_report

Lucas Kartawidjaja — Sun, 11 Nov 2012 02:29:42 GMT

I also got the same issue. As Eric Zierdt mentioned, the problem is with multiple victimProcess nodes.

I modified a script that Jonathan Kehayias' wrote on SQL Server Central. The script can be found on:

http://www.lucasnotes.com/2012/11/deadlock-graph-xml-from-extended-events.html.

Hopefully it can be helpful.

re: Parsing Extended Events xml_deadlock_report

Lorrin Ferdinand — Thu, 15 Nov 2012 17:41:37 GMT

Hi,

I would like to get a gauge on what the performance impact of running the xml_deadlock_report query to extract just that event is? I started running one on a system that was multi-core with 64 GBs of RAM and after 2 mins 15 seconds - I killed it. Should it take that long to run?

re: Parsing Extended Events xml_deadlock_report

Ali — Fri, 16 Nov 2012 16:01:41 GMT

Hi Lucas,

I also get error with your script.

Msg 9436, Level 16, State 1, Line 1

XML parsing: line 4, character 15, end tag does not match start tag

This Deadlock from DMV really not working for me. Not much usefull.

re: Parsing Extended Events xml_deadlock_report

Ali — Fri, 16 Nov 2012 18:45:12 GMT

Figured out another way to get information. First select into temp table without converting to XML.

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph

into tempdb.dbo.deadlock

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'

--Next CAST into xml

select CAST(deadlockgraph as XML) from tempdb.dbo.deadlock

re: Parsing Extended Events xml_deadlock_report

Dali — Fri, 08 Mar 2013 05:25:03 GMT

Thanks for nice deadlock report I wonder why there is no db name mentioned in the report?