结束SQL阻塞的进程
01
--结束SQL阻塞的进程％％％％％％％％％％％％％％％％％％％％％％
02
create procedure sp_Kill_lockProcess
03
as
04
begin
05
set NOCOUNT on
06
declare @spid int ,
07
@bl int ,
08
@intTransactionCountOnEntry int ,
09
@intRowcount int ,
10
@intCountProperties int , www.2cto.com
11
@intCounter int ,
12
@sSql nvarchar(200)
13
14
create table #tmp_lock_who ( id int identity(1, 1) ,
15
spid smallint ,
16
bl smallint )
17
18
if @@ERROR <> 0
19
return @@ERROR
20
21
insert into #tmp_lock_who ( spid, bl )
22
select 0, blocked
23
from ( select *
24
from sysprocesses
25
where blocked > 0 ) a
26
where not exists ( select *
27
from ( select *
28
from sysprocesses
29
where blocked > 0 ) b
30
where a.blocked = spid )
31
union
32
select spid, blocked
33
from sysprocesses
34
where blocked > 0
35
36
if @@ERROR <> 0
37
return @@ERROR
38
39
-- 找到临时表的记录数 www.2cto.com
40
select @intCountProperties = count(*), @intCounter = 1
41
from #tmp_lock_who
42
43
if @@ERROR <> 0
44
return @@ERROR
45
46
while @intCounter <= @intCountProperties
47
begin
48
-- 取第一条记录
49
select @spid = spid, @bl = bl
50
from #tmp_lock_who
51
where Id = @intCounter
52
begin
53
if @spid = 0
54
begin
55
set @sSql = 'kill ' + cast(@bl as varchar(10))
56
exec sp_executesql @sSql
57
end
58
end
59
60
-- 循环指针下移
61
set @intCounter = @intCounter + 1
62
end
63
64
drop table #tmp_lock_who
65
set NOCOUNT off
66
return 0
67
end
68
69
GO