SQL Server Agent – Updating Job’s Next Run Value

jobssql-server-agent

Every so often, my company's BI team will request the today's execution of a job be skipped. What I've been doing in the past, is going into the job schedule and manually changing the schedule's start date to tomorrow. Then, I click on OK on the job schedule to save the schedule edit and then OK on the job properties window.

I then see that the Next Run value change from today at noon to tomorrow at noon which is the desired behavior. Instead of doing it manually, I'd like perform the same task using a script. I tried updating the active_start_date column for the schedule in [msdb].[dbo].[sysschedules] but the job ended up running at noon and it had to be manually stopped.

Does someone know how to effectively skip the next execution for a job through the use of a script?

Environment: SQL Server 2012 SP1

Best Answer

I have some code that did this; here's the relevant line:

EXECUTE msdb.dbo.sp_update_schedule @schedule_id         = @target_schedule_id
                                   ,@enabled             = 1
                                   ,@active_start_date   = @new_rundate
                                   ,@active_start_time   = @new_active_start_time

NOTE: In may case, I was updating job schedules a few days in advance, to allow a different job to run the last business day before a holiday. I seem to recall that the job scheduler may cache some data, so it's possible that doing this ten minutes before the job is supposed to run will not have the desired effect.

That said: I always double-checked that the scheduled looked right in the Job Activity Monitor after I ran my script, and they always did.

Related Question