sql server 当前运行死锁语句查询

来源：互联网发布：中国贴吧知乎编辑：程序博客网时间：2024/05/16 19:04
use masterDECLARE @spid INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @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,       blockedFROM   (SELECT *        FROM   master..sysprocesses        WHERE  blocked > 0)aWHERE  NOT EXISTS(SELECT *                  FROM   master..sysprocesses                  WHERE  a.blocked = spid                         AND blocked > 0)UNIONSELECT spid,       blockedFROM   master..sysprocessesWHERE  blocked > 0 --if @@error<>0 return @@error    SELECT @count = Count(*),       @index = 1FROM   #temp_who_lock --select @count,@index --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语法如下'             PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk));            if(@spid <> 0)            BEGIN               DBCC inputbuffer(@spid)   --             END             DBCC inputbuffer(@blk)   --引起阻塞语句             SET @index=@index + 1        END  END DROP TABLE #temp_who_lock
0 0