Post reply

Extracting the clientapp for the victim of a deadlock report

  • April 5, 2022 at 9:07 am

    #4010927

    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?

  • April 6, 2022 at 9:10 am

    #4011496

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

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

  • 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