[SOLVED] SQL agent job failing at a step despite error handling

SQL agent job failing at a step despite error handling

My SQL Server instance has an agent job called Grand Master that runs to a schedule every minute, 24/7.

I have created another job that needs to be run manually from time to time. One of the first things it needs to do is disable and stop the Grand Master job from running while it is active.

Step 1 is to disable the GM, which works fine:

exec msdb..sp_update_job @job_name = "Grand Master", @Enabled = 0

Step 2, however fails. Its job it to stop the GM from running IF it is running. It is not supposed to do anything if the GM is not currently running:

if exists (select   1
           from msdb.dbo.sysjobs_view j
           join msdb.dbo.sysjobactivity a on j.job_id = a.job_id
           where a.run_requested_date is not null
             and a.stop_execution_date is null
             and j.name = 'Grand Master')
begin
    exec msdb.dbo.sp_stop_job 'Grand Master'
end

Every time I run this job, regardless of the state of the GM, it fails on step 2 with this error:

Executed as user: NT AUTHORITY\SYSTEM. SQLServerAgent Error: Request to stop job Grand Master (from User NT AUTHORITY\SYSTEM) refused because the job is not currently running. [SQLSTATE 42000] (Error 22022). The step failed.

Does anyone have any ideas?

Solution

First stop it if it's running and then disable the job. SQL Server might misinterprete the idea to stop a disabled job...