结束SQL阻塞的进程

2019独角兽企业重金招聘Python工程师标准>>>

--结束SQL阻塞的进程
create    procedure sp_Kill_lockProcess
as beginset NOCOUNT ondeclare @spid int ,@bl int ,@intTransactionCountOnEntry int ,@intRowcount int ,@intCountProperties int ,@intCounter int ,@sSql nvarchar(200)create table #tmp_lock_who ( id int identity(1, 1) ,spid smallint ,bl smallint )if @@ERROR <> 0 return @@ERRORinsert into #tmp_lock_who ( spid, bl )select 0, blockedfrom ( select *from sysprocesseswhere blocked > 0 ) awhere not exists ( select *from ( select *from sysprocesseswhere blocked > 0 ) bwhere a.blocked = spid )unionselect spid, blockedfrom sysprocesseswhere blocked > 0if @@ERROR <> 0 return @@ERROR-- 找到临时表的记录数select @intCountProperties = count(*), @intCounter = 1from #tmp_lock_whoif @@ERROR <> 0 return @@ERRORwhile @intCounter <= @intCountProperties begin-- 取第一条记录select @spid = spid, @bl = blfrom #tmp_lock_whowhere Id = @intCounterbeginif @spid = 0 beginset @sSql = 'kill ' + cast(@bl as varchar(10))exec sp_executesql @sSqlendend-- 循环指针下移set @intCounter = @intCounter + 1enddrop table #tmp_lock_whoset NOCOUNT offreturn 0endGO

转载于:https://my.oschina.net/mikezhang/blog/84268

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！