当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)