3

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.

3
  • 1
    Can you tell what, exactly, the job is still doing? Is it waiting on something? What does sp_Whoisactive tell you? Jun 20, 2016 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)? Mar 29, 2017 at 19:08
  • Yes over a linked server Mar 30, 2017 at 13:28

1 Answer 1

3

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.

1
  • 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, 2016 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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