How to convert an XML deadlock file into a graph or something readable?

Question

I'm looking for a easy or manageable way to read an XML file, specifically the XML output for SQL Server deadlocks. I have some servers that are not being monitored for various reasons.

I found the following code on the interwebz, but its output is nothing but XML.

CREATE TABLE #errorlog (
                        LogDate DATETIME 
                        , ProcessInfo VARCHAR(100)
                        , [Text] VARCHAR(MAX)
                        );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text
FROM #errorlog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT 
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
    AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Is there a way to take the raw XML output from this T-SQL code and display it as a deadlock graph or more easily readable text?

Try saving the XML as a file with an extension of xdl and then open in SSMS — Scott Hodgin - Retired, Oct 16 '17 at 23:20
To get it into tabular form you can use http://blog.waynesheffield.com/wayne/code-library/deadlocks/shred-deadlock-graph/ — Martin Smith, Oct 17 '17 at 06:02
@ScottHodgin That worked! It kills me to know that I've been in IT as long as I have and didn't think about file extension trick :| I should've known better, thank you! — AKDiscer, Oct 17 '17 at 13:44
Wanna receive *.xdl files in your Inbox in near real-time? It's fairly simple with event notifications — Dave Mason, Oct 17 '17 at 16:39
@ScottHodgin this works: changing the xml extension to xdl. . But also, delete the outer tags event, data, type value. the outer tag should be . — Phillip Ngan, Nov 19 '19 at 01:46
Sql Sentry Plan Explorer does a nice job of displaying deadlock graphs. — user2368632, Jan 10 '21 at 19:30
@MartinSmith This is the only working solution I have found to get it into tabular format. Thank you very much for sharing. — Asher, Oct 29 '21 at 12:56

score 6 · Accepted Answer · answered Oct 17 '17 at 02:17

You can bcp out and save it as .xdl. Many ways you can do it .. e.g. BCP OUT (I am using xp_cmdshell but you can use powershell or any other method).

Borrowing code from my answer (You need to adjust the code as per your requirement) :

-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!

exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!

Thank you, Kin. I can see how this method could be very useful! — AKDiscer, Oct 17 '17 at 13:45

How to convert an XML deadlock file into a graph or something readable?

1 Answers1