need to kill long running SPIDs

Question

need to kill long running SPIDs

rajesh yadav 291

hi,

i have seen in my server sometimes some SPIDs get stuck. so we find the logest running SPIDs and kill them . resons are many but in the last i have to kill them.

so kindly tel me how to automate this task. on internet some one told me to, use following in job sheduler and then use loop to kill using following query.

q1) is_user_porcess does the job or i might unintensally kill some server jobs?

q2) is there any other suitable way to do it?

please answer for web edition and also express edition.

SELECT  s.is_user_process ,
r.total_elapsed_time,
    r.session_id AS SPID,
    r.status,
    r.cpu_time,
    r.total_elapsed_time AS Duration_ms,
    r.blocking_session_id,
    t.text AS Query_Text
FROM sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.total_elapsed_time > 10000
and s.is_user_process =1
ORDER BY r.total_elapsed_time DESC;
--Do NOT kill:(These are system SPIDs)
--SPID = 1
--SPIDs < 50

2 answers

Your answer

Answer 1

When automating the termination of long-running SPIDs, you should ensure that only true user sessions are targeted and that system or maintenance-related processes are never affected. Relying solely on is_user_process = 1 is helpful, but might not be sufficient on its own, because some background tasks (such as SQL Agent jobs) can still appear as user processes. To avoid accidentally killing essential work, you might want to add other filters: excluding SPIDs below 50, avoiding SQL Agent, backup, and reporting service sessions, and protecting the session executing the cleanup script itself.

This should be suitable for SQL Server Web Edition when implemented as a SQL Agent job. For SQL Server Express Edition, it can be run via Windows Task Scheduler using sqlcmd. In both cases, the logic remains the same: identify long-running user processes, filter out protected sessions, and kill only the remaining SPIDs in a controlled loop.

DECLARE 
      @MaxDurationMs INT = 10000,
      @SPID INT,
      @Cmd NVARCHAR(100);

DECLARE kill_cursor CURSOR FAST_FORWARD FOR
SELECT r.session_id
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.total_elapsed_time > @MaxDurationMs
  AND s.is_user_process = 1
  AND r.session_id > 50
  AND s.program_name NOT LIKE 'SQLAgent%'
  AND s.program_name NOT LIKE 'Backup%'
  AND s.program_name NOT LIKE 'Report%'
  AND s.session_id <> @@SPID;

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @SPID;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Cmd = N'KILL ' + CAST(@SPID AS NVARCHAR(10));
    EXEC (@Cmd);
    FETCH NEXT FROM kill_cursor INTO @SPID;
END

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Erland Sommarskog 128.6K MVP Volunteer Moderator

Killing a process without knowing what it's doing sounds like a very bad idea. There can be jobs that perfectly natural runs for a long time. So I would advice against automating such a process.

Share via

need to kill long running SPIDs

2 answers

Your answer