How to using Procedure found Lead Blocker
/*
I found this script by Kalen Delaney on this site. I think this may help your situation:
Listing 1: Procedure That Finds the Head of a Chain of Blockers
*/
USE master
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='sp_leadblocker' AND type='P')
DROP PROC sp_leadblocker
GO
CREATE PROCEDURE sp_leadblocker
AS
IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT
spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,
dbname=substring(db_name(dbid),1,10),cmd,
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
ELSE
SELECT "No blocking processes found!"
go
I found this script by Kalen Delaney on this site. I think this may help your situation:
Listing 1: Procedure That Finds the Head of a Chain of Blockers
*/
USE master
SET QUOTED_IDENTIFIER OFF
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='sp_leadblocker' AND type='P')
DROP PROC sp_leadblocker
GO
CREATE PROCEDURE sp_leadblocker
AS
IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT
spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,
dbname=substring(db_name(dbid),1,10),cmd,
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
ELSE
SELECT "No blocking processes found!"
go