對一個加鎖的表進(jìn)行解鎖

　　點(diǎn)評use master 必須在master數(shù)據(jù)庫中創(chuàng)建 go if exists (select * from dbosysobjects where id = object_id(N[dbo][p_lockinfo]) and OBJECTPROPERTY(id NIsProcedure) = ) drop procedure [dbo][p_lockinfo] GO /*處理死鎖 查看當(dāng)前進(jìn)程或死鎖進(jìn)程并能自

use master 必須在master數(shù)據(jù)庫中創(chuàng)建
go
if exists (select * from dbosysobjects where id = object_id(N[dbo][p_lockinfo]) and OBJECTPROPERTY(id NIsProcedure) = )
drop procedure [dbo][p_lockinfo]
GO
/*處理死鎖
查看當(dāng)前進(jìn)程或死鎖進(jìn)程并能自動殺掉死進(jìn)程
因?yàn)槭轻槍λ赖?fp class='fp-q1c0k'>所以如果有死鎖進(jìn)程只能查看死鎖進(jìn)程
當(dāng)然你可以通過參數(shù)控制不管有沒有死鎖都只查看死鎖進(jìn)程
感謝: caiyunxiajiangopen 兩位提供的參考信息
鄒建 */
/*調(diào)用示例
exec p_lockinfo
*/
create proc p_lockinfo
@kill_lock_spid bit= 是否殺掉死鎖的進(jìn)程 殺掉 僅顯示
@show_spid_if_nolock bit= 如果沒有死鎖的進(jìn)程是否顯示正常進(jìn)程信息 顯示 不顯示
as
declare @count int@s nvarchar()@i int
select id=identity(int)標(biāo)志
進(jìn)程ID=spid線程ID=kpid塊進(jìn)程ID=blocked數(shù)據(jù)庫ID=dbid
數(shù)據(jù)庫名=db_name(dbid)用戶ID=uid用戶名=loginame累計(jì)CPU時間=cpu
登陸時間=login_time打開事務(wù)數(shù)=open_tran 進(jìn)程狀態(tài)=status
工作站名=hostname應(yīng)用程序名=program_name工作站進(jìn)程ID=hostprocess
域名=nt_domain網(wǎng)卡地址=net_address
into #t from(
select 標(biāo)志=死鎖的進(jìn)程
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 標(biāo)志=正常的進(jìn)程
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()@標(biāo)志 varchar()
while @i<=@count
begin
select @spid=進(jìn)程ID@標(biāo)志=標(biāo)志 from #t where id=@i
insert #t exec(dbcc inputbuffer(+@spid+))
if @標(biāo)志=死鎖的進(jìn)程 exec(kill +@spid)
set @i=@i+
end
end
else
while @i<=@count
begin
select @s=dbcc inputbuffer(+cast(進(jìn)程ID as varchar)+) from #t where id=@i
insert #t exec(@s)
set @i=@i+
end
select a*進(jìn)程的SQL語句=bEventInfo
from #t a join #t b on aid=bid
end
go