SQL Query to run in SQL Server Agent to Kill a process

xdyibdwo  于 2023-04-28  发布在  SQL Server
关注(0)|答案(1)|浏览(126)

SQL Query to run in SQL Server Agent to Kill all processes with wait time over 2 minutes and by host name.

I want to run the job every 10 seconds and monitor processes from a select few host names whose wait times are >2 minutes. I put together the following:

select 'kill ' 
    + cast(session_id as varchar(20)) 
    + cast(session_id as varchar(20))
from sys.dm_exec_sessions
where host_name = 'TS2012' and wait_time = '120000'

Tried the following code and it will kill the hosts but cant get the wait time to resolve.

select 'kill ' 
    + cast(session_id as varchar(20)) + cast(session_id as varchar(20))
from sys.dm_exec_sessions
where host_name = 'TS2012' and wait_time = '120000'
sf6xfgos

sf6xfgos1#

wait time is in another view -- join to it to filter on it. Like this:

select 'kill ' 
    + cast(s.session_id as varchar(20))
from sys.dm_exec_sessions s
join sys.dm_exec_session_wait_stats w on s.session_id = w.session_id
where s.host_name = 'TS2012' and w.wait_time_ms >= 120000

相关问题