need to kill long running SPIDs

Question

Post reply

need to kill long running SPIDs

rajemessage 14195

Hall of Fame

Points: 3351
More actions
December 4, 2025 at 9:37 am

#4694378
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
```

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323754 More actions · Answer 1

connections that have been established for a long time are not evil.

you mention things are "stuck", but your query is not looking for that. it;s just looking at long connecitosn.

On my prod server, i have six continuous connections from some app/web servers. the longest of which has a total_elapsed_time of 268,281,132. that's not a bad thing, and killing it would disrupt the business potentially.

add some where statements that look for the host name, program name, or other properties for the specific thing that is stuck.

WHERE hs.ostname = 'TheOffendingServer' and s.program_name = 'MyApplication' etc.

SELECT  s.is_user_process ,
r.total_elapsed_time,
    r.session_id AS SPID,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    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 1=1
--AND r.total_elapsed_time > 10000
and s.is_user_process =1
ORDER BY r.total_elapsed_time DESC;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajemessage 14195 Hall of Fame Points: 3351 More actions · Answer 2

I also have one long running connetion running a loop calling a stored procedure, it is used for syncing, but i have seen few other heavy stored procedure which we have sheduled to run every 15 min they get stuck for 3 hours to 4 hours which should have taken only mints.

i know that it could be some bad sql written some where , it could be some chain of deadlocks , but here is what u should under stand is this, the current situation is such. so kindly provide me the asked questions answer.

This reply was modified 2 weeks, 5 days ago by rajemessage 14195.