SQL agent job failing at a step despite error handling

Question

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?

Answer 1

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

Answer 2

If you have a disordered working environment, the above query may show different results than Job Activity Monitor (imho, GUI info is more reliable). You can use the next procedure to check if a job is in "Executing" state. The procedure is provided at your own responsibility.

/* procedure result: 0=Not idle or suspended, 1=Executing, 2=WaitingForThread, 3=BetweenRetries, 4=Idle, 5=Suspended, [6=WaitingForStepToFinish], 7=PerformingCompletionActions */

    CREATE PROCEDURE [dbo].[sp_get_job_state] (
    @job_name VARCHAR(100)
    , @job_state SMALLINT OUTPUT
    )
AS
BEGIN
    DECLARE @job_id UNIQUEIDENTIFIER
        , @can_see_all_running_jobs INT = 1
        , @job_owner SYSNAME = SUSER_SNAME()
        , @res SMALLINT;
    DECLARE @xp_results TABLE (
        job_id UNIQUEIDENTIFIER NOT NULL
        , last_run_date INT NOT NULL
        , last_run_time INT NOT NULL
        , next_run_date INT NOT NULL
        , next_run_time INT NOT NULL
        , next_run_schedule_id INT NOT NULL
        , requested_to_run INT NOT NULL
        , -- BOOL
        request_source INT NOT NULL
        , request_source_id SYSNAME COLLATE database_default NULL
        , running INT NOT NULL
        , -- BOOL
        current_step INT NOT NULL
        , current_retry_attempt INT NOT NULL
        , job_state INT NOT NULL
        );

    SELECT @job_id = job_id
    FROM msdb..sysjobs
    WHERE name = @job_name;

    INSERT INTO @xp_results (
        job_id
        , last_run_date
        , last_run_time
        , next_run_date
        , next_run_time
        , next_run_schedule_id
        , requested_to_run
        , request_source
        , request_source_id
        , running
        , current_step
        , current_retry_attempt
        , job_state
        )
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs = @can_see_all_running_jobs
        , @job_owner = @job_owner
        , @job_id = @job_id;

    SELECT @job_state = job_state
    FROM @xp_results
END