Technical Article

Extracting only Deadlock details from ErrorLog

,

Copy the script and run in SQL Server Management Studio.

use master
 
select @@SERVERNAME
go
SET NOCOUNT ON
DECLARE @startdate datetime
SELECT @startdate = '2014-08-01 01:00:00.000' --Choose your date.
DECLARE @CMD char(50)
DECLARE @rowcount int
DECLARE @roll int
DECLARE @spid varchar(20)
 
DECLARE @ProcessInfo varchar(40)
DECLARE @etext varchar(5000)
DECLARE @logdate datetime
 
create table #error (
sno int identity(1,1),
logdate datetime,
ProcessInfo varchar(40),
etext varchar(5000)
)
select @cmd='master..xp_readerrorlog'
insert into #error (logdate,ProcessInfo,etext) exec(@cmd)
SELECT @rowcount = SCOPE_IDENTITY() , @roll=1
delete #error where logdate @roll)
BEGIN
select @logdate=logdate,@ProcessInfo=ProcessInfo,@etext=etext from #error where sno=@roll 
if charindex('Deadlock encountered .... Printing deadlock information',@etext,1)>0
BEGIN
PRINT REPLICATE('*',100)
            SELECT @spid=@ProcessInfo
END
if charindex(@spid,@ProcessInfo,1)>0
BEGIN
PRINT rtrim(@logdate) + space(8) + @spid + space(8) + isnull(@etext,'NULL')
END
select @roll=@roll+1
      
END
PRINT REPLICATE('*',100)
drop table #error

Read 5,738 times
(16 in last 30 days)

Rate

3.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

3.86 (7)

You rated this post out of 5. Change rating