Extracting the clientapp for the victim of a deadlock report

Question

Extracting the clientapp for the victim of a deadlock report

Default port

Points: 1478

April 5, 2022 at 9:07 am

Given a deadlock report, I need to extract the clientapp that was the victim process. The query I've written works and is below:

DECLARE @path [nvarchar](MAX)

SELECT TOP 1
    @path = REPLACE([path] + '\system_health*.xel', '\\', '\')
FROM
    [sys].[dm_os_server_diagnostics_log_configurations]
WHERE
    [is_enabled] = 1

DECLARE @data TABLE ([deadlock] [xml], [exec_time] [datetime])

INSERT INTO
    @data
SELECT
    CONVERT(XML, [event_data]).query('/event/data/value/child::*') AS [XML],
    CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, CONVERT(XML, [event_data]).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','DATETIME')), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS [Execution_Time]
FROM
    [sys].[fn_xe_file_target_read_file](@path, NULL, NULL, NULL)
WHERE
    [object_name] = 'xml_deadlock_report'

SELECT
    [data].[exec_time],
    [d2].[Process].query('.').value('(process/@clientapp)[1]','NVARCHAR(MAX)')
FROM
    @data AS [data]
CROSS APPLY
    [deadlock].nodes('/deadlock/process-list/process') AS [d2]([Process])
WHERE
    CONVERT(NVARCHAR(MAX), [d2].[Process].query('.')) LIKE '%id="' + [data].[deadlock].value('(/deadlock/victim-list/victimProcess/@id)[1]', 'NVARCHAR(MAX)') + '"%'
ORDER BY
    [exec_time]
GO

I'm curious as to whether there's a better way to select the victim process than the concatenated WHERE clause?

I know I can pull the correct value using:

[d2].[Process].query('.').value('(process[@id="process280ba78a8c8"]/@clientapp)[1]','NVARCHAR(MAX)')

for example, but I can't find a way to set that dynamically. If I try:

[d2].[Process].query('.').value('(process[@id=' + [data].[deadlock].value('(/deadlock/victim-list/victimProcess/@id)[1]', 'NVARCHAR(MAX)') + '"]/@clientapp)[1]','NVARCHAR(MAX)')

I get the error:

Msg 8172, Level 16, State 1, Line 27
The argument 1 of the XML data type method "value" must be a string literal.

Is the concatenated WHERE clause the only option?

May 3, 2022 at 2:55 pm

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.