.::: Check xml_deadlock_report offline & online :::.

 

correlation deadlock 

https://teguhth.blogspot.com/2026/03/simalation-locking-blocking-deadlock-in.html
https://teguhth.blogspot.com/2021/09/sql-server-how-to-simulate-and-catch.html

1. check targent data  event_file

SELECT create_time,target_name,name,session_source,
    CAST(xt.target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets xt
JOIN sys.dm_xe_sessions xs
ON xs.address = xt.event_session_address
WHERE xs.name='system_health'
AND xt.target_name='event_file';

output 

<EventFileTarget truncated="0">
  <Buffers logged="4" dropped="0" />
  <File name="C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health_0_134303098936130000.xel" />
</EventFileTarget>
 

 
2. check xml deadlock

SELECT
    object_name,
    file_name,
    timestamp_utc,
    CAST(event_data AS XML) AS EventData
FROM sys.fn_xe_file_target_read_file
(
    N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health*.xel',
    NULL,
    NULL,
    NULL
)
WHERE object_name = 'xml_deadlock_report'
ORDER BY timestamp_utc DESC;

gmt +7 

SELECT
    object_name,
    file_name,

    CONVERT(datetime2(3), DATEADD(HOUR, 7, timestamp_utc)) AS Date_Jakarta,

    CAST(event_data AS XML) AS EventData

FROM sys.fn_xe_file_target_read_file
(
    N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health*.xel',
    NULL,
    NULL,
    NULL
)
WHERE object_name = 'xml_deadlock_report'
ORDER BY Date_Jakarta DESC;
 

3. read even_data.xml xml_deadlock_report

DECLARE @FileName NVARCHAR(260) = 'C:\backup\EventData_teguhth.xml';
DECLARE @OnlyFileName NVARCHAR(260);
DECLARE @SQL NVARCHAR(MAX);

-- Ambil nama file saja
SET @OnlyFileName =
RIGHT(@FileName, CHARINDEX('\', REVERSE(@FileName)) - 1);

SET @SQL = N'
DECLARE @x XML;

SELECT @x = BulkColumn
FROM OPENROWSET
(
    BULK ''' + @FileName + ''',
    SINGLE_BLOB
) AS A;

SELECT DISTINCT

    ''' + @OnlyFileName + ''' AS FileName,
     P.value(''@hostname'',''nvarchar(256)'') AS HostName,

    P.value(''@clientapp'',''nvarchar(256)'') AS ClientApp,
    P.value(''@spid'',''int'') AS SPID,

    P.value(''@loginname'',''nvarchar(256)'') AS LoginName,

    DB_NAME(P.value(''@currentdb'',''int'')) AS DatabaseName,

    (
        SELECT TOP (1)
            R.value(''@objectname'',''nvarchar(300)'')
        FROM @x.nodes(''/event/data/value/deadlock/resource-list/*'') X(R)
    ) AS ObjectName,

    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM @x.nodes(''/event/data/value/deadlock/victim-list/victimProcess'') V(X)
            WHERE V.X.value(''@id'',''varchar(100)'')
                  =
                  P.value(''@id'',''varchar(100)'')
        )
        THEN ''Korban''
        ELSE ''Bukan Korban''
    END AS StatusDeadlock,

    P.value(''(inputbuf/text())[1]'',''nvarchar(max)'') AS DeadlockQuery

FROM @x.nodes(''/event/data/value/deadlock/process-list/process'') T(P)

ORDER BY SPID;
';

EXEC sp_executesql @SQL;

 

4. read even_data.xml xml_deadlock_report with proces id

DECLARE @FileName NVARCHAR(260) = 'C:\backup\EventData_teguhth.xml';
DECLARE @OnlyFileName NVARCHAR(260);
DECLARE @SQL NVARCHAR(MAX);

-- Ambil nama file saja
SET @OnlyFileName =
RIGHT(@FileName, CHARINDEX('\', REVERSE(@FileName)) - 1);

SET @SQL = N'
DECLARE @x XML;

SELECT @x = BulkColumn
FROM OPENROWSET
(
    BULK ''' + @FileName + ''',
    SINGLE_BLOB
) AS A;

SELECT DISTINCT

    ''' + @OnlyFileName + ''' AS FileName,

    P.value(''@id'',''varchar(100)'')           AS SessionID,
    P.value(''@spid'',''int'')                  AS SPID,
    P.value(''@ecid'',''int'')                  AS ECID,

    P.value(''@hostname'',''nvarchar(256)'')    AS HostName,
    P.value(''@clientapp'',''nvarchar(256)'')   AS ClientApplication,
    P.value(''@clientpid'',''int'')             AS ClientPID,

    P.value(''@loginname'',''nvarchar(256)'')   AS LoginName,

    DB_NAME(P.value(''@currentdb'',''int''))    AS DatabaseName,

    P.value(''@isolationlevel'',''nvarchar(100)'') AS IsolationLevel,

    P.value(''@transactionname'',''nvarchar(100)'') AS TransactionName,

    P.value(''@lasttranstarted'',''datetime'') AS LastTranStarted,

    (
        SELECT TOP (1)
            R.value(''@objectname'',''nvarchar(300)'')
        FROM @x.nodes(''/event/data/value/deadlock/resource-list/*'') X(R)
    ) AS ObjectName,

    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM @x.nodes(''/event/data/value/deadlock/victim-list/victimProcess'') V(X)
            WHERE V.X.value(''@id'',''varchar(100)'')
                  =
                  P.value(''@id'',''varchar(100)'')
        )
        THEN ''Korban''
        ELSE ''Bukan Korban''
    END AS StatusDeadlock,

    P.value(''(inputbuf/text())[1]'',''nvarchar(max)'') AS DeadlockQuery

FROM @x.nodes(''/event/data/value/deadlock/process-list/process'') T(P)

ORDER BY SPID;
';

EXEC sp_executesql @SQL;