Capture the Deadlock-List from the Errorlog

What the script does

-------------------------

This script helps to identify the deadlock from the sql error log

Things to Note
-------------------
The script will only capture the deadlock in the most recent error log,
The first parameter in the "Sp_ReadErrorlog" needs to change in case the older deadlock are require.

Pre Requirements
------------------------
1204 or 1222 error flag needs to be enabled 


CREATE TABLE #Temp_All_LgData (ID int IDENTITY (1, 1), LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(max))
CREATE TABLE #Temp_Results (ID int IDENTITY (1,1), LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(max))
CREATE TABLE #Temp_DedLockID (ID int, processinfo nvarchar(50))

-- all the data in the error log will be recorded here
INSERT INTO #Temp_All_LgData
EXEC  sp_readerrorlog 0,1


--  only the records with "Deadlock-list" in the  text will be recorded
INSERT INTO #Temp_DedLockID
SELECT ID, processinfo
FROM #Temp_All_LgData
--WHERE LogText = 'deadlock-list' -- when trace flag 1222
WHERE LogText = 'Deadlock encountered .... Printing deadlock information' -- when trace flag 1204 is set

ORDER BY ID


DECLARE @StartID int, @ENDID int, @ProcessInfo nvarchar(50)
SELECT TOP 1 @StartID = ID FROM #Temp_DedLockID ORDER BY ID

WHILE(@@rowcount<>0)
BEGIN
    SELECT @ProcessInfo = ProcessInfo FROM #Temp_DedLockID WHERE ID = @StartID
    SELECT TOP 1 @ENDID = ID FROM #Temp_All_LgData WHERE ID > @StartID and ProcessInfo = @ProcessInfo ORDER BY ID DESC
   
    INSERT INTO #Temp_Results (LogDate, ProcessInfo, LogText)
    SELECT LogDate, ProcessInfo, LogText
    FROM #Temp_All_LgData
    WHERE
       ID >=@StartID and
       processinfo = @ProcessInfo and
       ID < @ENDID
    ORDER BY ID
   
    INSERT INTO #Temp_Results (LogDate, ProcessInfo, LogText)
    VALUES (GETDATE() , 'DEADLOCK   ' + @ProcessInfo  , 'END OF DEADLOCK   ' + @ProcessInfo ) ,(GETDATE() , 'NEW DEADLOCK' , '   NEXT DEADLOCK   ')
   
    DELETE #Temp_DedLockID WHERE ID = @StartID
    SELECT TOP 1 @StartID = ID FROM #Temp_DedLockID ORDER BY ID
      
   
   
END

SELECT LogDate, ProcessInfo, LogText
FROM #Temp_Results
ORDER BY ID



DROP TABLE #Temp_All_LgData
DROP TABLE #Temp_Results
DROP TABLE #Temp_DedLockID