MSSQL store procedure - kill deadlock

sp_KillDeadlocks.sql

USE YourDatabaseName; -- Replace 'YourDatabaseName' with your actual database name

CREATE PROCEDURE sp_KillDeadlocks
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @deadlockID INT;

    -- Create a temporary table to store deadlock information
    CREATE TABLE #DeadlockInfo
    (
        DeadlockID INT IDENTITY(1,1),
        DeadlockXML XML
    );

    -- Insert deadlock information into the temporary table
    INSERT INTO #DeadlockInfo (DeadlockXML)
    SELECT CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets AS st
    JOIN sys.dm_xe_sessions AS s ON s.address = st.event_session_address
    WHERE st.target_name = 'ring_buffer'
    AND s.name = 'system_health';

    -- Loop through the deadlock information and kill the deadlock process
    DECLARE deadlockCursor CURSOR FOR
    SELECT DeadlockID
    FROM #DeadlockInfo;

    OPEN deadlockCursor;
    FETCH NEXT FROM deadlockCursor INTO @deadlockID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @xml XML;
        SELECT @xml = DeadlockXML
        FROM #DeadlockInfo
        WHERE DeadlockID = @deadlockID;

        EXEC sp_executesql N'KILL ' + CAST((SELECT @xml.value('(/event/@timestamp)[1]', 'bigint')) AS NVARCHAR(50));

        FETCH NEXT FROM deadlockCursor INTO @deadlockID;
    END;

    CLOSE deadlockCursor;
    DEALLOCATE deadlockCursor;

    -- Drop temporary table
    DROP TABLE #DeadlockInfo;
END;