sql server 死锁查询

 

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;