SQL Server Home
Thank you for your feedback!
Invalid XML in Extended Events xml_deadlock_report output
- by Jonathan Kehayias
This item has been fixed in the current or upcoming version of this product.
A more detailed explanation for the resolution of this particular item may have been provided in the comments section.
1/22/2009 10:42:39 PM
The xml_deadlock_report provides an invalid xml document in the value output.
ATTACH A FILE
EDIT THIS ITEM
Item can only be reassigned when it is active.
to post a comment.
Please enter a comment.
Robert Heinig III
on 7/31/2015 at 5:19 AM
Ooooold hat, but I have a problem letting the demo scripts go in light of SQL 2012/14/16, just in case someone else stumbles upon this.
That "select .. where .. 'xml_deadlock_report'" will, on a more modern 2012sp2 box, run very inefficiently by default. Also, the xml cast shown is now based on wrong assumptions.
Replace the inner where with "where s.name = N'system_health' and st.target_name = N'ring_buffer'", omit the first slash from the nodes XPath, and use ".query('./data[@name="xml_report"]/value/deadlock')" for the XML fragment extraction. Why?
- SQL now comes with an 'event_file' target by default, and including that in the xquery takes "forever" (even though that target has no <RingBufferTarget>). The target_name filter reduces execution time on my dev box from >10min to 1s.
- // is to / like a scan to a seek (an oversimplification, but // should be slower anyway), and it is known that the <RingBufferTarget> is at the root.
- .value() extracts and concatenates all CData under the addressed node, it does not yield XML unless there's XML wrapped in escaped form into CData - which was the case in 2008R2SP3 but isn't in 2012+. Casting that to XML surprisingly (or not, as SQL XML allows fragments, not only well-formed XML, and pure CData is a valid fragment) succeeds as long as the parsed executionStack/frame/text() doesn't contain the wrong markup characters. query() is the correct way to extract the desired xml fragments.
on 11/5/2010 at 7:33 AM
This problem is also in SQL Server 2008 R2 and fixed in CU1
on 9/30/2010 at 11:43 AM
This issue was fixed in SQL Server 2008 SP1 CU6 (see http://support.microsoft.com/kb/978629 for details).
[MSFT] on 1/27/2009 at 8:46 AM
Thanks for bringing this to our attention. This issue is a side effect of changes made in the deadlock monitor for SQL 2008. The event raised via XEvent reflects the change in the deadlock XML that multiple victims per cycle can be found, where as SQL 2005 and before could only find one victim per cycle.
Unfortunately there is a problem in the xml generation that a block of XML is omitted. We are aggressively perusing a fix for this issue.
on 1/22/2009 at 10:47 PM
I uploaded scripts to produce a deadlock. First run the setup script, then run the Updater in one query window and the selector in another. Use the query included in the description below to retrieve the deadlock reports from XEvents.
I also included the output graph from sql trace and from XEvents as txt files for this problem.
to post a workaround.
Please enter a workaround.
Attach a file
Shared Deadlock Updater.sql (restricted)
Shared Deadlock Selecter.sql (restricted)
Shared Deadlock Setup.sql (restricted)
© 2017 Microsoft