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.

Improve this question
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? Commented 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. Commented 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. Commented May 29, 2019 at 19:37
  • @AaronBertrand thanks for the link, I'll be looking at that later. Commented 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. Commented May 30, 2019 at 16:26

3 Answers 3

Reset to default
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

Improve this answer
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. Commented 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. Commented 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. Commented 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.

Improve this answer
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. Commented 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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.