--- This table keeps track of the blocked operations
if exists (select * from pubs..sysobjects where name = 'BlockTable' and type = 'U')
  drop table pubs.dbo.BlockTable
go
create table pubs.dbo.BlockTable(
BlockedSid int,
BlockingSid int,
EventInfo1 varchar(300),
EventInfo2 varchar(300),
waitTime int
)
if exists (select * from master..sysobjects where name = 'usp_checkblocking' and type = 'P')
drop procedure usp_checkblocking
go
create procedure usp_checkblocking
as
	
	declare @spid int,@blocked int,@waittime int,@dbccstmt varchar(100)
	declare @EventType1 varchar(300),@EventType2 varchar(300)
	declare cur_sp cursor for select spid,blocked,waittime from master.dbo.sysprocesses where blocked > 1

	create table #dbcc_output (
	EventType varchar(30),
	Parameters varchar(30),
	EventInfo varchar(300)
	)
   	open cur_sp
        fetch next from cur_sp into @spid,@blocked,@waittime
	while (@@fetch_status = 0)
	begin
		
		set @dbccstmt = 'dbcc inputbuffer ('+convert(char(3),@spid)+')'
		print @dbccstmt
		insert  into #dbcc_output  exec (@dbccstmt)
		select @EventType1 = EventInfo from #dbcc_output
		truncate table #dbcc_output
		set @dbccstmt = 'dbcc inputbuffer ('+convert(char(3),@blocked)+')'
		insert  into #dbcc_output  exec (@dbccstmt)
		select @EventType2 = EventInfo from #dbcc_output
		truncate table #dbcc_output
		insert into pubs.dbo.BlockTable values (@spid,@blocked,@EventType1,@EventType2,@waittime)
		fetch next from cur_sp into @spid,@blocked,@waittime
	end
	close cur_sp
	deallocate cur_sp
	drop table #dbcc_output
go
---execute the script for each 5 seconds
declare @i int
set @i= 0
while @i < 10
begin  
execute usp_checkblocking
  waitfor delay '00:00:05'
  set @i =@i+1
end