结束SQL阻塞的进程

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

--结束SQL阻塞的进程
create    procedure sp_Kill_lockProcess
as 
    begin
        set NOCOUNT on
        declare @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 @@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
 
        while @intCounter <= @intCountProperties 
            begin
         -- 取第一条记录
                select @spid = spid, @bl = bl
                    from #tmp_lock_who
                    where Id = @intCounter
                begin
                    if @spid = 0 
                        begin
                            set @sSql = 'kill ' + cast(@bl as varchar(10))
                            exec sp_executesql @sSql
                        end
                end
  
  -- 循环指针下移
                set @intCounter = @intCounter + 1
            end
 
        drop table #tmp_lock_who
        set NOCOUNT off
        return 0
    end

GO

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