How To see parameter value on xml_deadlock_report?
This article will help us to retrieve the SQL Server deadlock information using the default system-health extended event session.You may consider it as a black box recorder to track the SQL instance activities.We do not need to enable trace flags 1204 and 1222 to capture the deadlocks using this extended event session. Simply run the below SQL query for see the each deadlock report in the XML file.
T-SQL Query:
DECLARE @xelfilepath NVARCHAR(260)
SELECT @xelfilepath
= dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @xelfilepath
= @xelfilepath +
N'system_health_*.xel'
DROP TABLE IF EXISTS
#TempTable
SELECT CONVERT(XML, event_data) AS EventData
INTO
#TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,
EventData.value('(event/@timestamp)[1]',
'VARCHAR(50)')),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS LocalTime,
EventData.query('event/data/value/deadlock')
AS XmlDeadlockReport
FROM
#TempTable
ORDER BY UtcTime DESC;
After execute this query, you will get the following deadlock information. Now click on each deadlock report and view the XML information in details. You can also save the required XML deadlock report in XSD format.
I hope this
will help you.