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,504 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