March 21, 2012 at 5:37 am
Hi
I am using the following query to extract deadlock info. Periodically I run the following query and extract the data into a table for future reference. My current SQL Version is SQL SERVER 2008 R2 Ent x64. In the following XML result , SQL Statements invloved in the dead lock are not displayed other than SQLHandle. Am I doing something wrong or is that how it is designed.
Query :
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'
OutPut :
<deadlock>
<victim-list>
<victimProcess id="process5e26e08" />
<victimProcess id="processb408988" />
</victim-list>
<process-list>
<process id="process5e26e08" taskpriority="0" logused="0" waitresource="KEY: 8:72057594765377536 (3ea05bd36b9d)" waittime="369" ownerId="140098" transactionguid="0x5dded373adb79a4d87aa63baee095b57" transactionname="DTCXact" lasttranstarted="2012-02-10T17:19:04.170" XDES="0xb69c1620" lockMode="RangeS-U" schedulerid="12" kpid="186360" status="suspended" spid="113" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-10T17:19:04.573" lastbatchcompleted="2012-02-10T17:19:04.573" clientapp="Microsoft® Windows® Operating System" hostname="" hostpid="1120" loginname="COMMON\svc_webapps_rio026" isolationlevel="serializable (4)" xactid="140098" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="16" stmtstart="778" stmtend="1348" sqlhandle="0x03000800d31a1c779f22f500aa9f00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 8 Object Id = 1998330579] </inputbuf>
</process>
<process id="processb408988" taskpriority="0" logused="116" waitresource="KEY: 8:72057594768982016 (ffffffffffff)" waittime="539" ownerId="140105" transactionguid="0x1a46fe180e03ec44a736efcc3762dd98" transactionname="DTCXact" lasttranstarted="2012-02-10T17:19:04.190" XDES="0xc278a5e0" lockMode="RangeI-N" schedulerid="1" kpid="189336" status="suspended" spid="112" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-10T17:19:04.417" lastbatchcompleted="2012-02-10T17:19:04.417" clientapp="Microsoft® Windows® Operating System" hostname="" hostpid="7280" loginname="COMMON\svc_webapps_rio026" isolationlevel="serializable (4)" xactid="140105" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="199" stmtstart="11734" stmtend="12444" sqlhandle="0x030008005ea7c8628922f500aa9f00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 8 Object Id = 1657317214] </inputbuf>
</process>
<process id="processb43ae08" taskpriority="0" logused="9772" waitresource="KEY: 8:72057594768982016 (0b4c1bd377f1)" waittime="408" ownerId="140092" transactionguid="0x058eb82f3f15c34482e719baf0f0b9d5" transactionname="DTCXact" lasttranstarted="2012-02-10T17:19:04.163" XDES="0xc278a080" lockMode="X" schedulerid="6" kpid="184368" status="suspended" spid="104" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-10T17:19:04.323" lastbatchcompleted="2012-02-10T17:19:04.323" clientapp="Microsoft® Windows® Operating System" hostname="" hostpid="6820" loginname="COMMON\svc_webapps_rio026" isolationlevel="serializable (4)" xactid="140092" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="192" stmtstart="11438" stmtend="11636" sqlhandle="0x030008005ea7c8628922f500aa9f00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 8 Object Id = 1657317214] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594765377536" dbid="8" objectname="" indexname="" id="lockbae1f80" mode="RangeS-U" associatedObjectId="72057594765377536">
<owner-list>
<owner id="processb43ae08" mode="RangeS-U" />
</owner-list>
<waiter-list>
<waiter id="process5e26e08" mode="RangeS-U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594768982016" dbid="8" objectname="" indexname="" id="lockba73100" mode="RangeS-S" associatedObjectId="72057594768982016">
<owner-list>
<owner id="processb43ae08" mode="RangeS-S" />
<owner id="process5e26e08" mode="RangeS-S" />
</owner-list>
<waiter-list>
<waiter id="processb408988" mode="RangeI-N" requestType="convert" />
</waiter-list>
</keylock>
<keylock hobtid="72057594768982016" dbid="8" objectname="" indexname="" id="lockb9d9300" mode="RangeS-U" associatedObjectId="72057594768982016">
<owner-list>
<owner id="processb408988" mode="RangeS-S" />
<owner id="process5e26e08" mode="RangeS-S" />
</owner-list>
<waiter-list>
<waiter id="processb43ae08" mode="X" requestType="convert" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
March 23, 2012 at 3:08 pm
I get an error when I run your query:
Msg 9436, Level 16, State 1, Line 1
XML parsing: line 44, character 12, end tag does not match start tag
Have you tried this one, from Paul Randal?
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_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';
Colleen
March 25, 2012 at 6:03 am
What is the SQL Server Version you are running on
March 26, 2012 at 1:57 pm
SQL Server 2008 R2, Enterprise Edition.
March 26, 2012 at 3:02 pm
Hi
We are on SQL Server 2008 R2 Ent with SP1 . If you are on RTM , its a good idea to get upto CU5 or above.
Cheers
Murali
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply