查看 MSSQL 死锁

当SQL Server有疑似死锁问题时，除了利用“活动和监视器”及 SQL Server Profile工具外，还可结合以下方法查找死锁源头

 

查看 MSSQL 资源死锁 

转自：https://blog.csdn.net/aroc_lo/article/details/5811597

CREATE Table #Who(spid int,
    ecid int,
    status nvarchar(50),
    loginname nvarchar(50),
    hostname nvarchar(50),
    blk int,
    dbname nvarchar(50),
    cmd nvarchar(50),
    request_id int
)

CREATE Table #Lock(spid int,
    dpid int,
    objid int,
    indld int,
    [Type] varchar(20),
    Resource varchar(50),
    Mode varchar(10),
    Status varchar(10)
)

INSERT INTO #Who
    EXEC sp_who active  --看哪个引起的阻塞，blk
INSERT INTO #Lock
    EXEC sp_lock  --看锁住了那个资源id，objid

DECLARE @DBName nvarchar(20);
SET @DBName='NameOfDataBase'

SELECT #Who.* FROM #Who WHERE dbname=@DBName
SELECT #Lock.* FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName;

--最后发送到SQL Server的语句
DECLARE crsr Cursor FOR
    SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;
DECLARE @blk int;
OPEN crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    dbcc inputbuffer(@blk)
    FETCH NEXT FROM crsr INTO @blk;
END
CLOSE crsr;
DEALLOCATE crsr;

--锁定的资源
SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName 
FROM #Lock
JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName
WHERE objid<>0

DROP Table #Who
DROP Table #Lock

use master
go

DECLARE @spid int, @bl int

DECLARE s_cur CURSOR FOR
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

OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
    if @spid =0
        select '引起数据库死锁的是:' + CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
    else
        select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'

    DBCC INPUTBUFFER (@bl )

    FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur

关于 DBCC INPUTBUFFER

转自： https://blog.csdn.net/wacthamu/article/details/8856216

DBCC INPUTBUFFER

显示从客户端发送到 Microsoft® SQL Server™ 的最后一个语句。

语法

DBCC INPUTBUFFER (spid)

参数

spid

是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。

 

结果集

DBCC INPUTBUFFER 返回包含如下列的行集。

列名	数据类型	描述
EventType	nvarchar(30)	事件类型，例如：RPC、语言或无事件。
Parameters	Int	0 = 文本 1- n = 参数
EventInfo	nvarchar(255)	对于 RPC 的 EventType，EventInfo 仅包含过程名。对于语言或无事件的 EventType，仅显示事件的头 255 个字符。

例如，当缓冲区中的最后事件是 DBCC INPUTBUFFER(11) 时，DBCC INPUTBUFFER 将返回以下结果集。

EventType      Parameters EventInfo             
-------------- ---------- --------------------- 
Language Event 0          DBCC INPUTBUFFER (11)

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

说明  在 Windows NT® 上，当两个事件间没有可以显示的事件时，有非常短的过渡期。在 Windows 98 上，事件仅当活动时才显示。

权限

DBCC INPUTBUFFER 权限默认授予 sysadmin 固定服务器角色的成员，该成员可以看到任何 SPID。其他用户可以看到自己拥有的 SPID。权限不可转让。

示例

下例假设有效的 SPID 为 10。

DBCC INPUTBUFFER (10)