查看 MSSQL 资源死锁

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

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