Labels

Query To Read Deadlock details From Error Log in SQL Server




--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'





No comments:
Write comments

Please do not enter spam links

Services

More Services