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

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