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?

Is this not just because sp_stop_job is trying to stop the job, and it is not running? Should you not check first to see if it is running, and if it is run sp_stop_job? — Keith, Sep 05 '17 at 10:51
Thanks for your comment @Leonidas199x The 'if exists...' query tests to see if the job is running. If the job is not running, that query returns an empty set and the step should do nothing and pass to step 3 — Cam, Sep 05 '17 at 10:53
Just a suggestion, but did you try to switch the two steps? First stop it if it's running and then disable the job? Possibly SQL Server does misinterprete the idea to stop a disabled job... — Tyron78, Sep 05 '17 at 11:00
@Tyron78 THANK YOU! When I was following this process manually I have always disabled first, but stopping first did the trick! — Cam, Sep 05 '17 at 11:03
Glad I could help. It would be kind of you to accept my answer. — Tyron78, Sep 05 '17 at 11:06

score 0 · Accepted Answer · answered Sep 05 '17 at 11:05

0

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

answered Sep 05 '17 at 11:05

Tyron78

4,117
2
17
32

score 0 · Answer 2 · answered May 29 '22 at 13:11

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

SQL agent job failing at a step despite error handling

2 Answers2