KB978629 - FIX: Error message when you use the system_health Extended Event session to capture a deadlock graph in SQL Server 2008: "Msg 9436: XML parsing: line 54, character 12, end tag does not match start tag"

Symptoms

Consider the following scenario. In a Microsoft SQL Server 2008 database, a deadlock occurs. Then, you use the system_health Extended Event session to capture the deadlock graph. In this scenario, you receive the following error message:

For example, you run the following query to export the buffer_ring target from the system_health session when a deadlock occurs:

select 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'

In this example, the query returns the correct result.

However, you can also run the following query to cast this deadlock graph in XML format:

select cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') 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'

In this situation, you expect to see the deadlock graph in XML format. Even if the deadlock graph is displayed incorrectly, you still expect to see valid XML content from the xml_deadlock_report report. However, you receive the following error message:

Cause

This problem occurs because the code path that generates the XML schema for the deadlock graph skips over the closing tag of the victim-list element. Additionally, the code path adds an unnecessary tag for the deadlock element.

Resolution

The fix for this issue was first released in Cumulative Update 6 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

977443 Cumulative update package 6 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems


For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates