Can a MSSQL job change its own active schedule?

Question

I am planning a new job for MSSQL Sever 2016.

I'd like the job to run on Sunday, every 10 minutes, until it sees a confirmation record is added to a table it checks as the final step in the job. If the record is there, the job modifies its current schedule so it will not run again until next Sunday. This leads me to wonder when the schedule updates in a "natural" job cycle. I wonder if my change would be overwritten before it could be used.

So, can I use msdb.dbo.sp_update_jobschedule in a job's final step to do this to the current job and what technical pitfalls might I encounter doing that?

Examples and/or alternate solutions are always welcomed.

Answer 1

You can, using the stored procedure sp_update_schedule

    declare @shcedule_id int

    if exists (select 1 from your_table where condition = 'met') begin

    select @shcedule_id  = s.schedule_id from sysjobs j
    inner join sysjobschedules s on j.job_id = s.job_id
    where j.name = 'test_job'


    sp_update_schedule @schedule_id = 9, @freq_type = 4, @freq_recurrence_factor = 1, 
    @active_start_date = 20190529, @active_start_time = 155500


    end

This will change it to weekly, recurring every one 1 week at 3:55 pm.

you can set it back to daily by setting @freq_type = 4

here's the Microsoft doc on the SP:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-2017

Answer 2

Some of this flow depends on how the job is actually structured. I am assuming that the job has one or more steps before it checks on the record being present for this flow The flow I would probably recommend would be along the lines of:

Job Start
    <Job body steps here>
    Check for Record
        Record does not exist?
            Kick off Job2
    Exit Job

Job2 Start
    Execute SQL Statement - WAITFOR DELAY '00:10:00'
    Kick off Job
    Exit Job2

Then, schedule Job for when you want it to start processing, and don't schedule Job2 at all. The result will be your job being executed every 10 minutes until the record is found, then not being restarted until the next scheduled date and time.

Answer 3

In case that you failed to use sp_update_schedule, you would be able to disable the job by using sp_update_job.

EXEC msdb.dbo.sp_update_job @enabled = 0, @job_name = N'test', @new_name = N'test -- Disabled'

I have tested it successfully, below is creation script.

USE [msdb]
GO

/****** Object:  Job [test]    Script Date: 5/30/2019 1:29:32 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 5/30/2019 1:29:32 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Do something]    Script Date: 5/30/2019 1:29:32 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Do something', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=4, 
        @on_success_step_id=2, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select 1', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Final Step]    Script Date: 5/30/2019 1:29:32 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Final Step', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC msdb.dbo.sp_update_job @enabled = 0, @job_name = N''test'', @new_name = N''test -- Disabled''', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO