熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQLServer死鎖的解除方法

2013-11-15 14:41:42  來源: SQL Server 

  SQL Server死鎖使我們經常遇到的問題下面就為您介紹如何查詢SQL Server死鎖希望對您學習SQL Server死鎖方面能有所幫助

  SQL Server死鎖的查詢方法

  exec masterdbop_lockinfo 顯示死鎖的進程不顯示正常的進程

  exec masterdbop_lockinfo 殺死死鎖的進程不顯示正常的進程

  SQL Server死鎖的解除方法

  Create proc p_lockinfo

  @kill_lock_spid bit= 是否殺掉死鎖的進程 殺掉 僅顯示

  @show_spid_if_nolock bit= 如果沒有死鎖的進程是否顯示正常進程信息 顯示 不顯示

  as

  declare @count int@s nvarchar(@i int

  select id=identity(int標志

  進程ID=spid線程ID=kpid塊進程ID=blocked數據庫ID=dbid

  數據庫名=db_name(dbid)用戶ID=uid用戶名=loginame累計CPU時間=cpu

  登陸時間=login_time打開事務數=open_tran 進程狀態=status

  工作站名=hostname應用程序名=program_name工作站進程ID=hostprocess

  域名=nt_domain網卡地址=net_address

  into #t from(

  select 標志=死鎖的進程

  spidkpidablockeddbiduidloginamecpulogin_timeopen_tran

  statushostnameprogram_namehostprocessnt_domainnet_address

  s=aspids=

  from mastersysprocesses a join (

  select blocked from mastersysprocesses group by blocked

  )b on aspid=bblocked where ablocked=

  union all

  select |_犧牲品_>

  spidkpidblockeddbiduidloginamecpulogin_timeopen_tran

  statushostnameprogram_namehostprocessnt_domainnet_address

  s=blockeds=

  from mastersysprocesses a where blocked<>

  )a order by ss

  select @count=@@rowcount@i=

  if @count= and @show_spid_if_nolock=

  begin

  insert #t

  select 標志=正常的進程

  spidkpidblockeddbiddb_name(dbid)uidloginamecpulogin_time

  open_transtatushostnameprogram_namehostprocessnt_domainnet_address

  from mastersysprocesses

  set @count=@@rowcount

  end

  if @count>

  begin

  create table #t(id int identity(a nvarchar(b IntEventInfo nvarchar())

  if @kill_lock_spid=

  begin

  declare @spid varchar(@標志 varchar(

  while @i<=@count

  begin

  select @spid=進程ID@標志=標志 from #t whereid=@i

  insert #t exec(dbcc inputbuffer(+@spid+

  if @標志=死鎖的進程 exec(kill+@spid)

  set @i=@i+

  end

  end

  else

  while @i<=@count

  begin

  select @s=dbcc inputbuffer(+cast(進程ID as varchar)+ from #t whereid=@i

  insert #t exec(@s)

  set @i=@i+

  end

  select a*進程的SQL語句=bEventInfo

  from #t a join #t b on aid=bid

  end


From:http://tw.wingwit.com/Article/program/SQLServer/201311/22183.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.