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.
Best Answer
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.