need to kill long running SPIDs

• rajemessage 14195

  Hall of Fame

  Points: 3345

  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