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, 112),
hostname=substring(hostname, 112),
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
posted @ 2012-07-17 11:55  Fandy Xie  Views(270)  Comments(0Edit  收藏  举报