SQL Server 查看死結的預存程序(轉載)

標籤：

if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[sp_who_lock]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)drop procedure [dbo].[sp_who_lock]GOuse mastergocreate procedure sp_who_lockasbegindeclare @spid int,@bl int,        @intTransactionCountOnEntry int,        @intRowcount int,        @intCountProperties int,        @intCounter int        create table #tmp_lock_who (         id int identity(1,1),         spid smallint,         bl smallint)        IF @@ERROR<>0 RETURN @@ERROR        insert into #tmp_lock_who(spid,bl) 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        IF @@ERROR<>0 RETURN @@ERROR -- 找到暫存資料表的記錄數        select @intCountProperties = Count(*),@intCounter = 1        from #tmp_lock_who        IF @@ERROR<>0 RETURN @@ERROR         if @intCountProperties=0            select ‘現在沒有阻塞和死結資訊‘ as message-- 迴圈開始while @intCounter <= @intCountPropertiesbegin-- 取第一條記錄        select @spid = spid,@bl = bl        from #tmp_lock_who where Id = @intCounter         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 )            end -- 迴圈指標下移        set @intCounter = @intCounter + 1enddrop table #tmp_lock_whoreturn 0end

 

SQL Server 查看死結的預存程序(轉載)