/*
清除指定数据库SLEEPING状态的连接, 从数据库端解决不规范代码造成数据库连接超多的问题
以下规则是根据机器进行分类统计, 当有超过100以上SLEEPING的连接数时,则清除历史90%的连接数
*/
create procedure ClearTheSleepingConnection
as
Begin
DECLARE @spid int
DECLARE @hostName varchar(50)
DECLARE CUR_Main CURSOR FOR
select HostName
from sys.sysprocesses s
inner join sys.databases d on s.dbid=d.database_id
where s.dbid=5 and s.status='sleeping' and s.Loginame in ('XXX', 'YYY')
group by HostName
having count(*) > 100
order by count(*) desc
OPEN CUR_Main
FETCH NEXT FROM CUR_Main INTO @hostName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CUR CURSOR
FOR SELECT TOP 90 percent spid FROM
sys.sysprocesses s
inner join sys.databases d on s.dbid=d.database_id
where s.dbid=5 and s.status='sleeping' and s.Loginame in ('XXX', 'YYY')
and HostName=@hostName
order by login_time
open CUR
FETCH NEXT FROM CUR INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('KILL ' + @spid )
FETCH NEXT FROM CUR INTO @spid
END
CLOSE CUR
DEALLOCATE CUR
FETCH NEXT FROM CUR_Main INTO @hostName
END
CLOSE CUR_Main
DEALLOCATE CUR_Main
end
|
