CODEWITHSUNDEEP
sqlsql-serverdeadlockextended-events
Paul Williams
Paul Williams

Reputation: 17040

Retrieve Timestamp of Deadlock in Extended Events Session Ring Buffer

I am trying to query the system_health extended events ring buffer for deadlock events. So far, I have gotten the deadlock graph like below. My apologies for a lack of reference; I have lost the page from which I found this query:

SELECT 
    CAST(event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS XML) AS DeadlockGraph
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (   -- Cast the target_data to XML 
        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 name = N'system_health' 
          AND target_name = N'ring_buffer' 
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)   
) AS tab (event_data);

I would also like to include a column for the timestamp of these events. If I run the inner query, I see the timestamp of the event is in the XML of the event itself:

<event name="xml_deadlock_report"
    package="sqlserver" id="123" version="1"
    timestamp="2013-07-23T16:25:25.495Z">

How can I parse this timestamp and display it? I have limited experience parsing XML with TSQL.

I've tried this:

SELECT
    CAST(event_data.value('/@timestamp', 'nvarchar(500)') AS datetime) AS [time],
    CAST(event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS XML) AS DeadlockGraph

But I get the error:

Msg 2390, Level 16, State 1, Line 2 XQuery [tab.event_data.value()]: Top-level attribute nodes are not supported

This:

CAST(event_data.value('(event/@timestamp)', 'nvarchar(500)') AS datetime) AS [time],

Yields this error:

Msg 2389, Level 16, State 1, Line 2 XQuery [tab.event_data.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Upvotes: 2

Views: 3597

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Change this

CAST(event_data.value('(event/@timestamp)', 'nvarchar(500)') AS datetime) AS [time],

with this

SELECT 
    event_data.value('(/event/@timestamp)[1]','VARCHAR(50)') AS TS1,
    event_data.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS TS2,
    ...

Results:

TS1                      TS2                               
------------------------ ----------------------------------
2013-07-23T17:49:46.072Z 2013-07-23 17:49:46.0720000 +00:00

Upvotes: 3

Related Questions