Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 20052008?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2008 (compatibility 2005) and the only copy of deadlocks have been extracted from the ring buffer using the below script:

Source Link

asked Jul 4, 2018 at 10:32

cloudsafe

305
1
3
14

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2005?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2005 and the only copy of deadlocks have been extracted from the ring buffer using the below script:

SELECT
   xed.value('@timestamp', 'datetime2(3)') as CreationDate,
   xed.query('.') AS XEvent
FROM
(
   SELECT CAST([target_data] AS XML) AS TargetData
   FROM sys.dm_xe_session_targets AS st
   INNER JOIN sys.dm_xe_sessions AS s
      ON s.address = st.event_session_address
   WHERE s.name = N'system_health'
         AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

The format is not compatible with our xdl viewers. Is there any way to get a diagram from the returned XML below?:

<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2018-07-04T00:15:14.667Z">
  <data name="xml_report">
    <type name="unicode_string" package="package0" />
    <value>&lt;deadlock&gt;
 &lt;victim-list&gt;
  &lt;victimProcess id="process3cf048"/&gt;
 &lt;/victim-list&gt;
 &lt;process-list&gt;
  &lt;process id="process3cf048" taskpriority="0" logused="0" waitresource="PAGE: 11:1:45050737" waittime="3573" ownerId="19491756" transactionname="user_transaction" lasttranstarted="2018-07-04T01:14:58.003" XDES="0x31b45b400" lockMode="S" schedulerid="4" kpid="2280" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-04T01:05:29.587" lastbatchcompleted="2018-07-04T01:05:29.587" clientapp="SQLAgent - TSQL JobStep (Job 0x3404686D1D7A2F478F69577F5EEBAE41 : Step 1)" hostname="ae-es" hostpid="5340" loginname="ae\rmsSYSTEM" isolationlevel="read committed (2)" xactid="19491756" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="2177" stmtstart="149730" stmtend="150314" sqlhandle="0x03000b00389b050021b69200bca300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="92" stmtstart="8170" stmtend="9080" sqlhandle="0x03000b00dea9f7571cb80601e6a300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="53" stmtstart="4514" stmtend="5792" sqlhandle="0x03000b00333d1b553bc7b200d3a300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="2" stmtstart="6" sqlhandle="0x01000b00b5d2e138601f5208040000000000000000000000"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;
    
exec EBB_PSF_ImportSales &amp;apos;G:\progra~1\es\winrms\import&amp;apos;   &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;process id="process4483b88" taskpriority="0" logused="6076" waitresource="PAGE: 11:1:132712" waittime="11747" ownerId="19493225" transactionname="user_transaction" lasttranstarted="2018-07-04T01:15:03.157" XDES="0x1344f8cc0" lockMode="S" schedulerid="6" kpid="5948" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-07-04T01:15:03.157" lastbatchcompleted="2018-07-04T01:15:03.153" lastattention="2018-07-03T23:45:37.360" clientapp="ECommerce WebService" hostname="ae-es" hostpid="3472" loginname="rmsSYSTEM" isolationlevel="read committed (2)" xactid="19493225" currentdb="11" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="359" stmtstart="36280" stmtend="39240" sqlhandle="0x03000b00bdc1b416537f2601dfa700000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="79" stmtstart="4356" stmtend="4452" sqlhandle="0x03000b00f6e5a817d68c2601dfa700000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="1" stmtstart="98" sqlhandle="0x01000b00c20b1a2dd036be87000000000000000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;
   
(@p0 xml,@p1 xml output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[EBB_PSFXML_Process] @xml = @p0, @xml_out = @p1 OUTPUT   &lt;/inputbuf&gt;
  &lt;/process&gt;
 &lt;/process-list&gt;
 &lt;resource-list&gt;
  &lt;pagelock fileid="1" pageid="45050737" dbid="11" objectname="" id="lockdb734b80" mode="IX" associatedObjectId="72057595923464192"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process4483b88" mode="IX"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process3cf048" mode="S" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/pagelock&gt;
  &lt;pagelock fileid="1" pageid="132712" dbid="11" objectname="" id="lock80131400" mode="IX" associatedObjectId="72057594217758720"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process3cf048" mode="IX"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process4483b88" mode="S" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/pagelock&gt;
 &lt;/resource-list&gt;
&lt;/deadlock&gt;
</value>
    <text />
  </data>
</event>

sql-server sql-server-2005 deadlock

Stack Exchange Network

Return to Question

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 20052008?

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2005?

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2008?

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2005?