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
此sql脚本要放在master库执行才可以,即要加上use master
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] nvarchar(20),
Resource nvarchar(50),
Mode nvarchar(10),
Status nvarchar(10)
);
INSERT INTO #Who
EXEC sp_who active --看哪个引起的阻塞,blk
INSERT INTO #Lock
EXEC sp_lock --看锁住了那个资源id,objid
DECLARE @DBName nvarchar(20);
SET @DBName='DBName'
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;