SQLSERVER对无用或闲置时间过长的SQL连接回收

时间：2010-11-22 来源：gflei

防止管理员做这种操作，因为这样反而会破坏连接池的作用。

存储过程完成对无用或闲置时间过长的SQL连接回收代码如下：

use master
go
create proc KillSpByDbName(@dbname varchar(20))
as
begin
declare @temp varchar(1000)
declare @spid int
declare getspid cursor for
select spid from sysprocesses a where dbid=db_id(@dbname) and status='sleeping' and (select datediff(n,last_batch,getdate()) from sysprocesses b where b.spid=a.spid)>10
open getspid
fetch next from getspid into @spid
while @@fetch_status <>-1
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end