blocked  and blocker  spid and details

use master

 /*create table in master database*/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BlockCheck]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BlockCheck]
GO

CREATE TABLE [dbo].[BlockCheck] (
	[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Waittime] [int] NULL ,
	[SPID_Blocked] [int] NULL ,
	[LastCmd_Blocked] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dbid] [int] NULL ,
	[CPU] [int] NULL ,
	[Pys_IO] [int] NULL ,
	[SPID_Blocker] [int] NULL ,
	[LastCmd_Blocker] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HostName_Blocked] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HostName_Blocker] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProgName_Blocked] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProgName_Blocker] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Login_Blocked] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Login_Blocker] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastBatch_Blocked] [datetime] NULL ,
	[LastBatch_Blocker] [datetime] NULL 
) ON [PRIMARY]
GO


/**********************/


CREATE PROCEDURE  BLOCK_CHECK

  @dbname   varchar(50)

AS



SET NOCOUNT ON


DECLARE @spid_blocked int ,
	@spid_blocker int,
	@cmd varchar(7000)
	

TRUNCATE TABLE  master..BlockCheck

   WHILE (1=1)
   BEGIN

CREATE TABLE #lock
	(spid int,
	dbid int,
	objid int,
	indid int,
	type varchar(10),
	resource varchar(40),
	mode varchar(10),
	status varchar(30))

CREATE TABLE #Blocked(
	EventType varchar(100) ,
	Parameters int ,
	EventInfo varchar(7000))


CREATE TABLE #Blocker(
	EventType varchar(100) ,
	Parameters int ,
	EventInfo varchar(7000))


INSERT INTO #lock EXEC sp_lock


INSERT INTO master..BlockCheck(Status, SPID_Blocked,dbid, CPU, Pys_IO, SPID_Blocker, HostName_Blocked, ProgName_Blocked, Login_Blocked, LastBatch_Blocked)
SELECT a.status, SPID, CPU,d.dbid, Physical_IO, Blocked, SUBSTRING(HostName, 1, 36), SUBSTRING(Program_Name, 1, 100), SUBSTRING(loginame, 1, 20), Last_Batch
FROM master..sysprocesses a, master..sysdatabases d
WHERE (a.blocked > 0
AND EXISTS (SELECT * FROM #lock  b WHERE  a.spid=b.spid and a.dbid=b.dbid)
 )
AND  a.SPID <> @@SPID
AND NOT EXISTS (SELECT * FROM master..BlockCheck c
WHERE a.blocked=c.SPID_Blocker
AND a.spid=c.SPID_Blocked)
AND a.dbid=d.dbid
AND d.name=@dbname


DECLARE Blocked CURSOR FAST_FORWARD FOR
SELECT SPID_Blocked FROM master..BlockCheck

OPEN Blocked

FETCH NEXT FROM  Blocked INTO @spid_blocked
WHILE @@FETCH_STATUS = 0
	BEGIN

	SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar, @spid_blocked) + ')'

	INSERT INTO #Blocked
	EXEC(@cmd)
	
	SELECT @cmd = EventInfo
	FROM #Blocked

	DELETE FROM #Blocked

	UPDATE master..BlockCheck
	SET LastCmd_Blocked = SUBSTRING(@cmd, 1, 1000)
	WHERE SPID_Blocked = @spid_blocked
	AND LastCmd_Blocked  IS NULL

	FETCH NEXT FROM Blocked INTO @spid_blocked

	END

CLOSE Blocked
DEALLOCATE Blocked


DECLARE Blocker CURSOR FAST_FORWARD FOR
SELECT SPID_Blocker FROM master..BlockCheck

OPEN Blocker

FETCH NEXT FROM Blocker INTO @spid_blocker
WHILE @@FETCH_STATUS = 0
	BEGIN

	SET @cmd = 'DBCC INPUTBUFFER(' + CONVERT(varchar,@spid_blocker)+ ')'

	INSERT INTO #Blocker
	EXEC(@cmd)
	
	SELECT @cmd = EventInfo
	FROM #Blocker

	DELETE FROM #Blocker

	UPDATE master..BlockCheck
	SET LastCmd_Blocker = SUBSTRING(@cmd, 1, 1000)
	WHERE SPID_Blocker =  @spid_blocker
	AND  LastCmd_Blocker IS NULL

	UPDATE master..BlockCheck
	SET HostName_Blocker =hostname,
        Progname_Blocker=program_name,
	Login_Blocker=loginame,
	LastBatch_Blocker=Last_Batch
	FROM  master..sysprocesses 
             WHERE spid= @spid_blocker
	AND  LastBatch_Blocker is null



	FETCH NEXT FROM Blocker INTO  @spid_blocker

	END

CLOSE Blocker
DEALLOCATE Blocker


  
   DROP TABLE #Blocker
   DROP TABLE #lock
   DROP TABLE #Blocked
 
  END

WAITFOR DELAY '00:00:20'
GO