查詢SQL資料庫死結

標籤：style   blog   color   io   使用   ar   資料   div   sp   

使用sqlserver作為資料庫的應用系統,都避免不了有時候會產生死結， 死結出現以後，維護人員或者開發人員大多隻會通過sp_who來尋找死結的進程，然後用sp_kill殺掉。利用sp_who_lock這個預存程序，可以很方便的知道哪個進程出現了死結，出現死結的問題在哪裡.

建立sp_who_lock預存程序

 

CREATE procedure sp_who_lock    as      begin         declare @spid int         declare @blk int         declare @count int         declare @index int         declare @lock tinyint          set @lock=0          create table #temp_who_lock        (         id int identity(1,1),         spid int,         blk int       )        if @@error<>0 return @@error        insert into #temp_who_lock(spid,blk)        select 0 ,blocked         from (select * from master..sysprocesses where blocked>0)a        where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)        union select spid,blocked from  master..sysprocesses where blocked>0        if @@error<>0 return @@error        select @count=count(*),@index=1 from #temp_who_lock        if @@error<>0 return @@error        if @count=0        begin        select ‘沒有阻塞和死結資訊‘        return 0        end       while @index<=@count        begin        if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid))         begin         set @lock=1          select @spid=spid,@blk=blk from #temp_who_lock where id=@index         select ‘引起資料庫死結的是: ‘+ CAST(@spid AS VARCHAR(10)) + ‘進程號,其執行的SQL文法如下‘         select  @spid, @blk        dbcc inputbuffer(@spid)          dbcc inputbuffer(@blk)         end        set @index=@index+1        end       if @lock=0         begin        set @index=1         while @index<=@count         begin         select @spid=spid,@blk=blk from #temp_who_lock where id=@index         if @spid=0           select ‘引起阻塞的是:‘+cast(@blk as varchar(10))+ ‘進程號,其執行的SQL文法如下‘         else           select ‘進程號SPID：‘+ CAST(@spid AS VARCHAR(10))+ ‘被‘ + ‘進程號SPID：‘+ CAST(@blk AS VARCHAR(10)) +‘阻塞,其當前進程執行的SQL文法如下‘         dbcc inputbuffer(@spid)        dbcc inputbuffer(@blk)          set @index=@index+1         end       end       drop table #temp_who_lock        return 0       end                GO 

 

查詢SQL資料庫死結