Query To Read Deadlock details From Error Log

--DROP TABLE results

GO

set nocount on

--BEGIN GATHER ALL DEADLOCK-RELATED RECORDS IN SQL SERVER ERROR LOG

create table #RawLogs  (

id int IDENTITY (1, 1), 

logdate datetime, 

processinfo nvarchar(50), 

logtext nvarchar(max)

)

insert into #RawLogs

exec sp_readerrorlog

CREATE table results  

(

id int IDENTITY (1,1),

deadlockcount int, 

logdate datetime, 

processinfo nvarchar(50), 

logtext nvarchar(max), 

oldid int

)

declare @dids table (id int, processinfo nvarchar(50), logdate datetime)

declare @rids table (id int, processinfo nvarchar(50), logdate datetime)

declare @wids table (id int, processinfo nvarchar(50), logdate datetime)

insert into @dids

select id, processinfo, logdate

from #RawLogs

where

logteXt = 'deadlock-list'

order by id

insert into @rids

select id, processinfo, logdate

from #RawLogs

where logteXt = '  resource-list'