查询Sql Server 2005的堵塞情况(死锁)

最新推荐文章于 2021-11-30 11:07:32 发布

转载最新推荐文章于 2021-11-30 11:07:32 发布 · 112 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jiguixin/archive/2011/01/10/1931814.html

文章标签：

#数据库

本文介绍了一个用于SQL Server的自定义存储过程sp_who_lock，该过程能够有效地检测并报告数据库中出现的死锁和阻塞情况，通过创建临时表来存储阻塞信息，并利用sys.sysprocesses视图来获取具体的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SET QUOTED_IDENTIFIER ON
 
GO
SET ANSI_NULLS ON
GO
 
CREATE PROCEDURE sp_who_lock
AS
    BEGIN
        DECLARE @spid INT ,
            @bl INT ,
            @intTransactionCountOnEntry INT ,
            @intRowcount INT ,
            @intCountProperties INT ,
            @intCounter INT
 
        CREATE TABLE #tmp_lock_who
            (
              id INT IDENTITY(1, 1) ,
              spid SMALLINT ,
              bl SMALLINT
            )
 
        IF @@ERROR <> 0
            RETURN @@ERROR
 
        INSERT INTO #tmp_lock_who ( spid, bl )
                SELECT 0, blocked
                    FROM ( SELECT *
                            FROM sys.sysprocesses
                            WHERE blocked > 0
                         ) a
                    WHERE NOT EXISTS ( SELECT *
                                        FROM ( SELECT *
                                                FROM sys.sysprocesses
                                                WHERE blocked > 0
                                             ) b
                                        WHERE a.blocked = spid )
                UNION
                SELECT spid, blocked
                    FROM sys.sysprocesses
                    WHERE blocked > 0
 
        IF @@ERROR <> 0
            RETURN @@ERROR
 
       -- 找到临时表的记录数
        SELECT @intCountProperties = COUNT(*), @intCounter = 1
            FROM #tmp_lock_who
 
        IF @@ERROR <> 0
            RETURN @@ERROR
 
        IF @intCountProperties = 0
            SELECT N'现在没有阻塞和死锁信息' AS message
       -- 循环开始
        WHILE @intCounter <= @intCountProperties
            BEGIN
              -- 取第一条记录
                SELECT @spid = spid, @bl = bl
                    FROM #tmp_lock_who
                    WHERE Id = @intCounter
                BEGIN
                    IF @spid = 0
                        SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))
                                + N'进程号,其执行的SQL语法如下'
                    ELSE
                        SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10))
                                + N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'
                    DBCC INPUTBUFFER (@bl )
                END
 
              -- 循环指针下移
                SET @intCounter = @intCounter + 1
            END
 
 
        DROP TABLE #tmp_lock_who
 
        RETURN 0
    END
go
EXEC sp_who_lock
DROP PROC  sp_who_lock
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO