check blocking

IF EXISTS(SELECT * FROM MASTER..SYSPROCESSES WITH(NOLOCK)
   WHERE BLOCKED<>0 AND SPID<>BLOCKED AND WAITTIME>30000 )
BEGIN
DECLARE @SQL NVARCHAR(4000)
SET @SQL=N'
--刪除臨時表
IF OBJECT_ID(''TEMPDB..#TMP_SYSPROCESSES'') IS NOT NULL
BEGIN
DROP TABLE #TMP_SYSPROCESSES
END
IF OBJECT_ID(''TEMPDB..#TMP_LOCK_WHO'') IS NOT NULL
BEGIN
DROP TABLE #TMP_LOCK_WHO
END

--定義變量
DECLARE @SPID INT
DECLARE @BL INT
DECLARE @INTTRANSACTIONCOUNTONENTRY INT
DECLARE @INTROWCOUNT INT
DECLARE @INTCOUNTPROPERTIES INT
DECLARE @INTCOUNTER INT

--創建臨時表
CREATE TABLE #TMP_LOCK_WHO (
ID INT IDENTITY(1,1),
SPID SMALLINT,
BL SMALLINT)

--獲取系統進程信息
SELECT * INTO #TMP_SYSPROCESSES
 FROM MASTER.DBO.SYSPROCESSES WITH(NOLOCK)

--尋找真凶
INSERT INTO #TMP_LOCK_WHO(SPID,BL)
SELECT 0 ,BLOCKED
FROM (SELECT * FROM #TMP_SYSPROCESSES WHERE BLOCKED>0 ) A
WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM #TMP_SYSPROCESSES WHERE BLOCKED>0 ) B WHERE A.BLOCKED=SPID)

--尋找幫凶
INSERT INTO #TMP_LOCK_WHO(SPID,BL)
SELECT SPID,BLOCKED
FROM #TMP_SYSPROCESSES WHERE BLOCKED>0
ORDER BY SPID ASC

-- 找到临时表的记录数
SELECT @INTCOUNTPROPERTIES = COUNT(*),@INTCOUNTER = 1
FROM #TMP_LOCK_WHO

-- IF @INTCOUNTPROPERTIES=0
-- SELECT N''现在没有阻塞信息'' AS MESSAGE

-- 循环开始
WHILE @INTCOUNTER <= @INTCOUNTPROPERTIES
BEGIN
 -- 取第一条记录
 SELECT @SPID = SPID,@BL = BL
 FROM #TMP_LOCK_WHO WHERE ID = @INTCOUNTER
 --獲取進程執行SCRIPTS
 IF @SPID =0
  BEGIN
  SELECT N''引起阻塞的是: ''+CONVERT(VARCHAR(10),@BL) + N''进程执行的SQL語句如下''
  DBCC INPUTBUFFER (@BL )
  SELECT  SPACE(2000)
  END
 ELSE
  BEGIN
  SELECT CONVERT(VARCHAR(10),@SPID)+N''进程被''+CONVERT(VARCHAR(10),@BL) +N''进程阻塞''
  SELECT CONVERT(VARCHAR(10),@SPID) + N''进程执行的SQL語句如下''
  DBCC INPUTBUFFER (@SPID )
  SELECT CONVERT(VARCHAR(10),@BL) + N''进程执行的SQL語句如下''
  DBCC INPUTBUFFER (@BL )
  SELECT  SPACE(2000)
 END
 -- 循环指针下移
 SET @INTCOUNTER = @INTCOUNTER + 1
END

--尋找凶手
SELECT
SPID
,BLOCKED AS BLKBY
,RTRIM(LOGINAME) AS 凶手_登录名
,RTRIM(HOSTNAME) AS 電腦名
,WAITTIME AS 已等待毫秒
,RTRIM(PROGRAM_NAME) AS 系統名
,RTRIM(LASTWAITTYPE) AS  等待类型
,RTRIM(WAITRESOURCE) AS  锁被资源
,CPU AS CPU使用
,PHYSICAL_IO AS 磁盘读写
,LOGIN_TIME AS 登录时间
,LAST_BATCH AS 上次执行
,STATUS AS 状态
FROM #TMP_SYSPROCESSES
WHERE SPID IN
 (SELECT BLOCKED 
 FROM  #TMP_SYSPROCESSES
 WHERE BLOCKED<>0 AND SPID<>BLOCKED )
---確定誰是主謀
ORDER BY WAITTIME ASC

SELECT  SPACE(2000)

--尋找受害者
SELECT
SPID
,BLOCKED AS BLKBY
,RTRIM(LOGINAME) AS 受害者登录名
,RTRIM(HOSTNAME) AS 電腦名
,WAITTIME AS 已等待毫秒
,RTRIM(PROGRAM_NAME) AS 系統名
,RTRIM(LASTWAITTYPE) AS  等待类型
,RTRIM(WAITRESOURCE) AS  锁被资源
,CPU AS CPU使用
,PHYSICAL_IO AS 磁盘读写
,LOGIN_TIME AS 登录时间
,LAST_BATCH AS 上次执行
,STATUS AS 状态
FROM  #TMP_SYSPROCESSES
WHERE BLOCKED<>0
AND SPID<>BLOCKED
--確定受害程度
AND WAITTIME>10
ORDER BY WAITTIME DESC'

DECLARE @SUBJECT  NVARCHAR(4000)
SET  @SUBJECT=@@SERVERNAME+N' BLOCKING '+CONVERT(VARCHAR(10), GETDATE(),21)
EXEC MASTER.DBO.XP_SENDMAIL
--@RECIPIENTS=' WILLIAMLAI@LEO.COM.HK;DAQUANLIN@LEO.COM.HK;TSINGLIANG@LEO.COM.HK'
@RECIPIENTS=' TSINGLIANG@LEO.COM.HK'
,@SUBJECT=@SUBJECT
,@QUERY=@SQL
,@ATTACH_RESULTS='TRUE'
,@WIDTH=4000

END