查询Sqlserver数据库死锁的一个存储过程

原文： 点击打开链接

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.1.35
 * Time: 2013/11/12 16:48:00
 ************************************************************/

IF OBJECT_ID('proc_who_lock') IS NOT NULL
BEGIN
    DROP PROC proc_who_lock
END
GO
CREATE PROCEDURE proc_who_lock
AS
BEGIN
	DECLARE @spid INT     
	DECLARE @blk INT     
	DECLARE @count INT     
	DECLARE @index INT     
	DECLARE @lock TINYINT      
	SET @lock = 0      
	CREATE TABLE #temp_who_lock
	(
		id       INT IDENTITY(1, 1),
		spid     INT,
		blk      INT
	)      
	IF @@error <> 0
	    RETURN @@error
	
	INSERT INTO #temp_who_lock
	  (
	    spid,
	    blk
	  )
	SELECT 0,
	       blocked
	FROM   (
	           SELECT *
	           FROM   MASTER..sysprocesses
	           WHERE  blocked > 0
	       )a
	WHERE  NOT EXISTS(
	           SELECT *
	           FROM   MASTER..sysprocesses
	           WHERE  a.blocked = spid
	                  AND blocked > 0
	       ) 
	UNION
	SELECT spid,
	       blocked
	FROM   MASTER..sysprocesses
	WHERE  blocked > 0
	
	IF @@error <> 0
	    RETURN @@error
	
	SELECT @count = COUNT(*),
	       @index = 1
	FROM   #temp_who_lock
	
	IF @@error <> 0
	    RETURN @@error
	
	IF @count = 0
	BEGIN
	    SELECT '没有阻塞和死锁信息' 
	    RETURN 0
	END
	
	WHILE @index <= @count
	BEGIN
	    IF EXISTS(
	           SELECT 1
	           FROM   #temp_who_lock a
	           WHERE  id > @index
	                  AND EXISTS(
	                          SELECT 1
	                          FROM   #temp_who_lock
	                          WHERE  id <= @index
	                                 AND a.blk = spid
	                      )
	       )
	    BEGIN
	        SET @lock = 1      
	        SELECT @spid = spid,
	               @blk = blk
	        FROM   #temp_who_lock
	        WHERE  id = @index
	        
	        SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10)) + 
	               '进程号,其执行的SQL语法如下'
	        
	        SELECT @spid,
	               @blk
	        
	        DBCC INPUTBUFFER(@spid) 
	        DBCC INPUTBUFFER(@blk)
	    END
	    
	    SET @index = @index + 1
	END     
	IF @lock = 0
	BEGIN
	    SET @index = 1      
	    WHILE @index <= @count
	    BEGIN
	        SELECT @spid = spid,
	               @blk = blk
	        FROM   #temp_who_lock
	        WHERE  id = @index
	        
	        IF @spid = 0
	            SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10)) + 
	                   '进程号,其执行的SQL语法如下'
	        ELSE
	            SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被' + 
	                   '进程号SPID：' + CAST(@blk AS VARCHAR(10)) + 
	                   '阻塞,其当前进程执行的SQL语法如下'
	        
	        DBCC INPUTBUFFER(@spid) 
	        DBCC INPUTBUFFER(@blk)      
	        SET @index = @index + 1
	    END
	END
	
	DROP TABLE #temp_who_lock 
	RETURN 0
END
GO
 
EXEC proc_who_lock