2

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.

5
  • How do you plan to change it back next Sunday? Wouldn't it be cleaner to just let it run every 10 minutes and quit with success if the record is there and it's not Sunday yet? May 29, 2019 at 19:24
  • @AaronBertrand It's more my OCD, I don't want the job history spammed with all the unneeded runs.
    – WillG
    May 29, 2019 at 19:36
  • Job history for successful runs is easy to keep clean (or only keeping the runs that made it to a certain step, for example). I wrote a starter piece here. I'm also thinking that if the record doesn't appear until Friday or Saturday that your OCD is on the wrong end of the deal. May 29, 2019 at 19:37
  • @AaronBertrand thanks for the link, I'll be looking at that later.
    – WillG
    May 29, 2019 at 21:35
  • Another idea: Job is executed once. Loop with a WAITFOR DELAY. When the condition is satisfied, exit. Optionally, also exit if it's no longer Sunday. Maybe THROW an error, if you want job history to highlight you didn't get the expected condition in your time window). No schedule modding, no history cleaning. May 30, 2019 at 16:26

3 Answers 3

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

3
  • Not that I doubt what you say, I'm having some issues testing it. If I run sp_update_schedule with a new active_start_date and active_start_time and then select from sysjobschedules, it still shows the old time and date in next run. It's confusing.
    – WillG
    May 29, 2019 at 22:07
  • Yea, I tried updating that table and it didn't work. If you open up the job and look at it's schedule, it should change though.
    – James
    May 30, 2019 at 11:19
  • 1
    So odd but it's not the first time I've seen such things. It seems to work regardless of what shows in sysjobschedules.
    – WillG
    May 31, 2019 at 16:13
0

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.

1
  • Using two jobs is an option. I may do that if I can't get the current solution to work like I want it to.
    – WillG
    May 29, 2019 at 22:10
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.