Extract and analyze deadlock events from SQL Server's system health extended event logs.

-- Step 1: Create a temporary table to store error log data
CREATE TABLE #errorlog (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    [Text] VARCHAR(MAX)
);

-- Step 2: Declare variables to extract log path
DECLARE @tag VARCHAR(MAX), @path VARCHAR(MAX);

-- Step 3: Populate the error log table using system stored procedure
INSERT INTO #errorlog EXEC sp_readerrorlog;

-- Step 4: Extract the log file path from the error log
SELECT @tag = [Text]
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

-- Step 5: Drop the temporary error log table
DROP TABLE #errorlog;

-- Step 6: Parse the log path from the extracted text
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

-- Step 7: Drop the #Deadlock table if it already exists
IF OBJECT_ID('#Deadlock') IS NOT NULL DROP TABLE #Deadlock;

-- Step 8: Read deadlock reports from the system health extended events file
SELECT  
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') AS Execution_Time
INTO #Deadlock
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME LIKE 'xml_deadlock_report'
  AND event_data LIKE '%BNY_HF_6840_0008%' -- Filter by database name
ORDER BY Execution_Time DESC;

-- Step 9: Retrieve the top 1000 deadlock events
SELECT TOP 1000 * 
FROM #Deadlock 
ORDER BY Execution_Time DESC;

-- Optional: Filter by specific time range
-- WHERE Execution_Time BETWEEN '2021-09-18 05:00:00.000' AND '2021-09-20 05:00:00.000'