We have some jobs that run via the scheduler. We noticed today that some are still running from the weekend, and we need to kill them.

We have used sp_stop_job and it reports that the job has been stopped sucessfully, but looking at the Job Activity Monitor that status is still Executing: 1 (Subplan_1). I am using various queries to get the jobs that are currently running and the jobs still appear.

SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]
FROM [msdb].[dbo].[sysjobs_view] sjv
JOIN [msdb].[dbo].[sysjobactivity] sja
ON [sjv].[job_id] = [sja].[job_id]
WHERE [sja].[run_requested_date] is not null
AND [sja].[stop_execution_date] is NULL

How is this possible? Is it just sending an ABORT command or similar? And can I really kill the jobs immediately?

I'm rather new to this side of SQL.

  • 1
    Can you tell what, exactly, the job is still doing? Is it waiting on something? What does sp_Whoisactive tell you? – Kris Gruttemeyer Jun 20 '16 at 12:53
  • Do any of these jobs make calls over a linked server or calls outside of SQL (e.g. such as a CmdExec or SSIS step)? – John Eisbrener Mar 29 '17 at 19:08
  • Yes over a linked server – VictorySaber Mar 30 '17 at 13:28

You should keep in mind that aborting a job does not always mean the job is aborted as expected. It could be that the transactions needed to be rolled back and this may also take a while depending on the amount of data and the performance.

  • And, just so that you know, sometimes SQL Agent jobs show that they are still running even though they failed hours or days earlier. – RLF Jun 20 '16 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.