Copy the script and run in SQL Server Management Studio.
Generate Deadlock Summary Information
This article will show you how to extract a summary of tables affected by deadlocks.
2016-12-13
3,594 reads
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