查看阻塞和死锁信息

  
--==================================================================================================================  
--   ProcedureName   :   sp_who_lock  
--   Author     :   作者不详,出自网络  
--   CreateDate    :      2013-05-13  
--   Description    :   查看阻塞和死锁信息  
/*******************************************************************************************************************  
  Parameters     :   参数说明  
********************************************************************************************************************  
                                       无参存储过程  
********************************************************************************************************************  
  Modified Date   Modified User     Version     Modified Reason  
********************************************************************************************************************  
       2013-06-03     Kerry  V01.00.01   调整存储过程格式,代码部分修改以及增加注释信息  
********************************************************************************************************************/  
--===================================================================================================================  
  
CREATE PROCEDURE sp_who_lock
AS      
BEGIN  
  
DECLARE @spid       INT;  
DECLARE @block       INT;  
DECLARE @RowCount      INT;  
DECLARE @RowIndex      INT;  
  
--创建临时表，保持被阻塞或正阻塞其他SQL的SQL语句信息  
CREATE TABLE #tmp_lock_who  
(  
      id INT IDENTITY(1, 1) ,  
      spid SMALLINT ,  
      block SMALLINT  
)  
      
      
IF @@ERROR<>0 RETURN @@ERROR;  
  
 INSERT INTO #tmp_lock_who  
        (  
          spid ,  
          block   
        )  
        SELECT  0 ,  
                blocked  
        FROM    ( SELECT    *  
                  FROM      sysprocesses  
                  WHERE     blocked > 0  
                ) a  
        WHERE   NOT EXISTS ( SELECT *  
                             FROM   ( SELECT    *  
                                      FROM      sysprocesses  
                                      WHERE     blocked > 0  
                                    ) b  
                             WHERE  a.blocked = spid )  
        UNION  
        SELECT  spid ,  
                blocked  
        FROM    sysprocesses  
        WHERE   blocked > 0;  
          
          
IF @@ERROR<>0 RETURN @@ERROR;  
  
  
-- 找到临时表的记录数  
SELECT  @RowCount = COUNT(1) ,  
        @RowIndex = 1  
FROM    #tmp_lock_who  
  
  
IF @@ERROR<>0 RETURN @@ERROR;  
  
IF  @RowCount=0  
SELECT  N'现在没有阻塞和死锁信息' AS MESSAGE;  
  
  
-- 循环开始  
WHILE @RowIndex <= @RowCount  
BEGIN  
 -- 取第一条记录  
 SELECT     @spid    = spid,  
      @block   = block  
 FROM #tmp_lock_who   
 WHERE Id = @RowIndex  
  
 IF @spid = 0  
  SELECT N'引起数据库死锁的是: ' + CAST(@block AS VARCHAR(10))  
   +  N'进程号,其执行的SQL语法如下';  
 ELSE  
  SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10)) + N'被进程号SPID：'  
   +  CAST(@block AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下';  
    
 DBCC INPUTBUFFER (@block )  
  
  
 SET @RowIndex = @RowIndex + 1;  
  
END;  
  
 DROP TABLE #tmp_lock_who;  
  
 RETURN 0;  
END