22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @sql NVARCHAR(MAX)
SET @sql=''
SELECT @sql='kill '+LTRIM(spid)+';' FROM sys.sysprocesses AS s
WHERE s.spid>50 --不是系统进程
AND s.spid<>@@SPID --不是当前进程
AND s.last_batch<DATEADD(HOUR,-4,GETDATE()) --最后执行时间是4小时之前
AND s.program_name='Microsoft SQL Server Management Studio' --从客户端连接
--输出要执行的SQL
PRINT @sql
--执行动态SQL
EXEC(@sql)
declare @tsql nvarchar(1000)
declare ap scroll cursor for
select distinct 'kill '+rtrim(spid)
from sys.sysprocesses
where spid>50
and status<>N'background'
and datediff(hh,last_batch,getdate())>8 --> 未执行SQL超过8小时
open ap
fetch from ap into @tsql
while(@@fetch_status<>-1)
begin
exec(@tsql)
fetch next from ap into @tsql
end
close ap
deallocate ap