.jpeg)
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(''@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;
.jpeg)
