Hi All.

I have a SQL 2008 R2 isntance, and I am trying to have a system which alerts after a deadlock. The deadlock traceflags are already in place, but this writes to the SQL error log, the purpose of my plan is to alert right after a deadlock so I have decided the best way to do this is using Xevents.

At the moment I have the query below to extract the deadlock information. I would like to shred the XML and retrieve details and save onto a table such as table name, victim, spid, sql statements etc. Any help would be appreciated.

SELECT
       xed.value('@timestamp', 'datetime') as Creation_Date,
       xed.query('.') AS Extend_Event
FROM
(
       SELECT CAST([target_data] AS XML) AS Target_Data 
       FROM sys.dm_xe_session_targets AS xt
       INNER JOIN sys.dm_xe_sessions AS xs
       ON xs.address = xt.event_session_address
       WHERE xs.name = N'system_health'
       AND xt.target_name = N'ring_buffer'
 
) AS XML_Data

The XML looks like this

<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2015-02-18T16:24:30.726Z">
  <data name="xml_report">
    <type name="unicode_string" package="package0" />
    <value>&lt;deadlock&gt;
 &lt;victim-list&gt;
  &lt;victimProcess id="process5c1ddc8"/&gt;
 &lt;/victim-list&gt;
 &lt;process-list&gt;
  &lt;process id="process5c1ddc8" taskpriority="0" logused="220" waitresource="KEY: 2:7926335401066364928 (c20918c6e169)" waittime="3800" ownerId="19070357" transactionname="user_transaction" lasttranstarted="2015-02-18T16:24:36.943" XDES="0x1469d9530" lockMode="X" schedulerid="9" kpid="5536" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-02-18T16:24:50.497" lastbatchcompleted="2015-02-18T16:24:36.947" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GBVA548376" hostpid="1208" loginname="INTL\A548376" isolationlevel="read committed (2)" xactid="19070357" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="1" stmtstart="30" sqlhandle="0x020000008407891bbd2cb7a0d4923843c79f4ce2254daada"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="1" sqlhandle="0x02000000dcc60f0386a6487bf053a44870ed1152ebeb6613"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;
INSERT dbo.Tbl2 (id, col) VALUES (111, 555)
   &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;process id="process5c31048" taskpriority="0" logused="3548" waitresource="KEY: 2:7854277806987476992 (61a06abd401c)" waittime="11378" ownerId="19061287" transactionname="user_transaction" lasttranstarted="2015-02-18T16:23:24.980" XDES="0x8695463b0" lockMode="S" schedulerid="11" kpid="14736" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2015-02-18T16:24:42.903" lastbatchcompleted="2015-02-18T16:24:20.723" lastattention="2015-02-18T16:20:51.050" clientapp="Microsoft SQL Server Management Studio - Query" hostname="GBVA548376" hostpid="1208" loginname="INTL\A548376" isolationlevel="read committed (2)" xactid="19061287" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="2" stmtstart="30" sqlhandle="0x020000008407891bbd2cb7a0d4923843c79f4ce2254daada"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="2" stmtstart="24" sqlhandle="0x020000008d2ab9030650ebc0eab587b94eebe42bfed9ea40"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;
BEGIN TRAN
INSERT dbo.Tbl2 (id, col) VALUES (111, 2)   &lt;/inputbuf&gt;
  &lt;/process&gt;
 &lt;/process-list&gt;
 &lt;resource-list&gt;
  &lt;keylock hobtid="7926335401066364928" dbid="2" objectname="" indexname="" id="lock67f7d80" mode="X" associatedObjectId="7926335401066364928"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process5c31048" mode="X"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process5c1ddc8" mode="X" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/keylock&gt;
  &lt;keylock hobtid="7854277806987476992" dbid="2" objectname="" indexname="" id="lock69b1100" mode="X" associatedObjectId="7854277806987476992"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process5c1ddc8" mode="X"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process5c31048" mode="S" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/keylock&gt;
 &lt;/resource-list&gt;
&lt;/deadlock&gt;
</value>
    <text />
  </data>
</event>

Here is some stuff that I wrote recently. It does not exactly meet what you are asking for. Particularly, I used SQL Trace to capture the deadlocks, and in the script below, I have already read the tracefile to a table. But the XML format for the deadlock is the same. (If you use trace, replace the table with a call to sys.fn_trace_gettable.)

It features the function crack_inputbuf. This one is required when the inputbuffer is a stored procedure call over RPC. All you see in this case is a database id and an object id. crack_inputbuf translates this to database name and procedure name. The procedure uses a table objmap, which you will need to build on your own by running

    SELECT db_name(), object_id, name
    FROM   sys.objects

in all databases. (But if you only have one really interesting database on the server, you can of course use sys.objects in this database directly.)

You asked about table name; I don't really do this, because requires a lot more work.

CREATE FUNCTION crack_inputbuf(@inputbuf    nvarchar(1000),
                                                             @DatabaseID smallint) RETURNS nvarchar(1000) AS
BEGIN
     RETURN (CASE WHEN @inputbuf NOT LIKE '%Proc %' THEN @inputbuf
                                ELSE isnull((SELECT name
                                            FROM     aba_sp..objmap o
                                            WHERE    db_id = @DatabaseID
                                                AND    objid = convert(int, substring(@inputbuf,
                                                                                        charindex('t Id =', @inputbuf) + 7,
                                                                                        charindex(']', @inputbuf) - (charindex('t Id =', @inputbuf) + 7)))),
                                             @inputbuf)
                    END)
END
go
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'deadlocks')
     DROP TABLE deadlocks
go
WITH CTE AS (
     SELECT t.EventSequence, t.ReportTime,
                    B.prc.value('@spid', 'int') AS spid,
                    parsename(C.frame.value('@procname', 'nvarchar(400)'), 3) AS dbname,
                    CASE WHEN    B.prc.value('@trancount', 'tinyint') > 0
                             THEN B.prc.value('@lasttranstarted', 'datetime')
                    END AS lasttran,
                    B.prc.value('@lastbatchstarted', 'datetime') AS lastbatch,
                    B.prc.value('@clientapp', 'sysname') AS app,
                    B.prc.value('@loginname', 'sysname') AS login,
                    B.prc.value('@waitresource', 'varchar(100)') AS waitresource,
                    B.prc.value('@lockMode', 'varchar(20)') AS lockmode,
                    CASE WHEN A.deadlock.value('@victim', 'varchar(20)') =
                                        B.prc.value('@id', 'varchar(20)')
                             THEN 'VICTIM'
                             ELSE ''
                    END AS victim,
                    dbo.crack_inputbuf(B.prc.value('(inputbuf/text())[1]', 'nvarchar(1000)'),
                                                         B.prc.value('@currentdb', 'smallint')) AS inputbuf,
                    parsename(C.frame.value('@procname', 'nvarchar(400)'), 1) AS procname,
                    C.frame.value('(./text())[1]', 'nvarchar(MAX)') AS query,
                    C.frame.value('@stmtstart', 'int') AS stmtstart,
                    t.TextData AS fullmonty
     FROM    (SELECT EventSequence, convert(time(0), StartTime) AS ReportTime,
                                 try_convert(xml, TextData) AS deadlock,
                                 convert(nvarchar(MAX), TextData) AS TextData
                    FROM     locktrc20150114
                    WHERE    EventClass = 148) AS t
     CROSS APPLY t.deadlock.nodes ('/deadlock-list/deadlock') AS A(deadlock)
     CROSS APPLY A.deadlock.nodes('process-list/process') AS B(prc)
     CROSS APPLY B.prc.nodes('executionStack/frame[1]') AS C(frame)
), aggr AS (
     SELECT dense_rank() OVER (ORDER BY ReportTime, dbname) AS deadlockno,
                    row_number() OVER (PARTITION BY ReportTime, dbname ORDER BY spid) AS rowno,
                    ReportTime, dbname, spid,
                    MAX(lasttran) AS lasttran, MAX(lastbatch) AS lastbatch, MAX(app) AS app,
                    MAX(login) AS login, MAX(waitresource) AS waitresource, MAX(lockmode) AS lockmode,
                    MAX(victim) AS victim, MAX(inputbuf) AS inputbuf, MAX(procname) AS procname,
                    MAX(stmtstart) AS stmtstart, MAX(query) AS query, MAX(fullmonty) AS fullmonty
     FROM     CTE
     GROUP    BY ReportTime, dbname, spid
)
SELECT deadlockno, ReportTime, spid, dbname, lasttran,
             lastbatch, app, login, waitresource, lockmode, victim,
             inputbuf, procname, stmtstart, query,
             CASE WHEN rowno = 1 THEN convert(xml, fullmonty) END AS fullmonty
INTO     deadlocks
FROM     aggr
ORDER    BY deadlockno, spid
go
ALTER TABLE deadlocks ALTER COLUMN deadlockno int NOT NULL
ALTER TABLE deadlocks ALTER COLUMN spid int NOT NULL
go
ALTER TABLE deadlocks ADD CONSTRAINT pk_deadlocks PRIMARY KEY (deadlockno, spid)
go
DROP FUNCTION crack_inputbuf
go
WITH CTE AS (
     SELECT deadlockno, splist
     FROM     (SELECT DISTINCT deadlockno FROM deadlocks) AS d
     CROSS    APPLY (SELECT DISTINCT procname + '(' + ltrim(str(stmtstart)) + ')    '
                                 FROM     deadlocks d2
                                 WHERE    d.deadlockno = d2.deadlockno
                                 ORDER    BY 1
                                 FOR XML PATH('')) AS d2 (splist)
)
SELECT splist, COUNT(*)
FROM     CTE
GROUP    BY splist
ORDER    BY 2 DESC 

For parsing the SQL Server 2008 R2 system_health-deadlock graph I use the following code

SELECT
	-- Everything:
	--	XEvent.query('.') AS DeadlockGraph
	--,
		---- Pre- SQL 2008 SP2: 
		--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

		 --> SQL 2008 R2 SP2 (10.50.2500):
		CAST(
			XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') 
			AS xml)
		AS DeadlockGraph

	-- TimeStamp			
	--,	XEventData.XEvent.value('(@timestamp)[1]', 'varchar(max)')	 AS timestamp
	-- adjust timezone
	,	DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), XEventData.XEvent.value('(@timestamp)[1]', 'varchar(max)'))  AS timestamp

FROM (
	SELECT CAST(target_data as xml)		AS TargetData
	FROM sys.dm_xe_session_targets		AS dm_xe_session_targets

		INNER JOIN sys.dm_xe_sessions AS dm_xe_sessions
			ON dm_xe_session_targets.event_session_address = dm_xe_sessions.address

	WHERE
		dm_xe_sessions.name = 'system_health'
	) AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE
	XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

If your system is on the latest SP + CU this code will work and you don't need to revert to the old SQL Trace technology or even TraceFlags at all.

I'm about to finalize my deadlock-collector as a codeplex project, which parses the deadlock information into a database table even further. Once it's out I'll post a link.