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

xdyibdwo 于 2023-04-28 发布在 SQL Server

关注(0)|答案(1)|浏览(127)

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'

sql-server

来源：https://stackoverflow.com/questions/76124419/sql-query-to-run-in-sql-server-agent-to-kill-a-process

1条答案

按热度按时间

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

赞(0）回复(0）举报 2023-04-28

我来回答

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

1条答案

相关问题

热门标签

最新问答