transaction 641 deadlock sql server 2008 r2

Transaction 641 Deadlock in SQL Server 2008 R2

In my previous work on Extended Events in SQL Server, I have encountered a significant breaking change that has unfortunately affected several of my scripts written for SQL Server 2008 R2. The issue arises when trying to retrieve the deadlock graph from the system_health session using the xml_deadlock_report event.

The problem is that SQL Server 2008 R2 treats XML data elements differently than its predecessor, SQL Server Denali CTP3. In SQL Server 20R2, the query used to retrieve the deadlock graph was (excluding the workaround included in the original article since the xml_deadlock_report was fixed in a later Cumulative Update and the latest Service Pack for SQL Server 2008 R2):

`SELECT CAST(target_data AS XML) AS TargetData CROSS APPLY TargetData.nodes('XEventData/XEvent') AS XEventData`

In contrast, when running this same code in SQL Server Denali CTP3, the output will not be the xml_deadlock_report but instead the textual data that was included in the sub-nodes of the value node for the xml_deadlock_report events in the target.

This change is a significant one and has implications for anyone working with Extended Events in SQL Server 2008 R2. Specifically, scripts written to expect the XML output as text in the value element will need to be modified to accommodate this new behavior.

I noticed this breaking change while validating my demos for the 24 Hours of PASS and my PASS Summit 2011 Precon – Extended Events Deep Dive. The impact of this change is significant, and I hope that this article will help others who may also be affected by this change in SQL Server 2008 R2.

Transaction 641 deadlock sql server 2008 r2