[SQL SERVER][TSQL] Kill Process
利用SQL Agent Job 定期kill sleeping process
declare @dbname varchar(150),@mykillsql varchar(20)
, @curspid int,@step int,@maxcount int
set @dbname='QEIP'
set @step=1
declare @myresult table
(spid int,ecid int, status varchar(150), loginname varchar(150),
hostname varchar(150), blk int, dbname varchar(150),
cmd varchar(150),request_id int
)
INSERT INTO @myresult
EXEC sp_who
select @maxcount=count(*)
from @myresult
where spid>50
and dbname=@dbname
and [status] ='sleeping'--Suspended
and spid <> @@spid
while(@step <=@maxcount)
begin
select @curspid=spid
from (
select spid,row_number() over(order by spid) as 'myrows'
from @myresult
where spid>50
and dbname=@dbname
and [status] ='sleeping'--Suspended
and spid <> @@spid
) tbl
where myrows=@step
set @mykillsql='KILL '+ cast(@curspid as varchar(5))
--print @mykillsql
exec (@mykillsql)
set @step=@step+1
end