首頁 > 其他 > 詳細

查看死鎖

時間:2019-06-19 09:54:55      閱讀:29      評論:0      收藏:0      [點我收藏+]

標簽:try   who   table   語法   阻塞   err   class   declare   spro   

declare @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 SELECT @@ERROR
 
 insert into #tmp_lock_who(spid,bl) select  0 ,blocked
   from (select * from sys.sysprocesses where  blocked>0 ) a 
   where not exists(select * from (select * from sys.sysprocesses  where  blocked>0 ) b 
   where a.blocked=spid)
   union select spid,blocked from sys.sysprocesses  where  blocked>0

 IF @@ERROR<>0 SELECT @@ERROR 
  
-- 找到臨時表的記錄數
 select  @intCountProperties = Count(*),@intCounter = 1
 from #tmp_lock_who
 
 IF @@ERROR<>0 SELECT @@ERROR 
 
 if @intCountProperties=0
  select ‘現在沒有阻塞和死鎖信息‘ as message

-- 循環開始
while @intCounter <= @intCountProperties
begin
-- 取第一條記錄
  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 + 1
end

drop table #tmp_lock_who

  

查看死鎖

標簽:try   who   table   語法   阻塞   err   class   declare   spro   

原文:https://www.cnblogs.com/blogs2014/p/11049492.html

(0)
(0)
   
舉報
評論 一句話評論(0
0條  
登錄后才能評論!
? 2014 bubuko.com 版權所有 魯ICP備09046678號-4
打開技術之扣,分享程序人生!
             

魯公網安備 37021202000002號

湖南快乐十分钟走势图