I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in sysjobschedules and sysschedules, I can see that the new schedule has been added and is tied to the job_id for my SQL Agent job. However, the values for next_run_date and next_run_time have 0 in them. When I come back and look at them again in 2 or 3 minutes, they still show 0's in them. However when I come back another 5 or 10 minutes later, it now correctly shows the date and time values corresponding to the next scheduled run.
So my questions are:
- How often do these values get updated?
- What process is it that updates these values?
- If I were to add a schedule that was, say, 1 minute in the future, does that mean that the job will not run since the next_run_date/time haven't been updated yet?
Example of the code I use to add a new schedule:
exec msdb.dbo.sp_add_jobschedule @job_id = @jobID
, @name = @JobName
, @enabled = 1
, @freq_type = 1
, @freq_interval = 0
, @freq_subday_type = 0
, @freq_subday_interval = 0
, @freq_relative_interval = 0
, @freq_recurrence_factor = 0
, @active_start_date = @ScheduleRunDate
, @active_end_date = 99991231
, @active_start_time = @ScheduleRunTime
, @active_end_time = 235959
where @jobID is binary(16) that holds the job_id of the job in question, @ScheduleRunDate and @ScheduleRunTime are INTs with the date and time respectively.
Best Answer
Short Answer
It looks like the data in
msdb.dbo.sysjobschedules
is updated by a background thread in SQL Agent, identified asSQLAgent - Schedule Saver
, every 20 minutes (or less frequently, ifxp_sqlagent_notify
has not been called and no jobs have run in the meantime).For more accurate information, look at
next_scheduled_run_date
inmsdb.dbo.sysjobactivity
. This is updated in real-time any time a job is changed or a job has run. As an added bonus, thesysjobactivity
stores the data the right way (as a datetime column), making it a lot easier to work with than those stupid INTs.That's the short answer:
Long Answer
If you care to follow the rabbit for a moment, when you call
sp_add_jobschedule
, this chain of events is set into motion:Now, we can't chase the rabbit any further, because we can't really peek into what
xp_sqlagent_notify
does. But I think we can presume that this extended procedure interacts with the Agent service and tells it that there has been a change to this specific job and schedule. By running a server-side trace we can see that, immediately, the following dynamic SQL is called by SQL Agent:It seems that
sysjobactivity
is updated immediately, andsysjobschedules
is only updated on a schedule. If we change the new schedule to be once a day, e.g.We still see the immediate update to
sysjobactivity
as above, and then another update after the job is finished. Various updates come from background and other threads within SQL Agent, e.g.:A background thread (the "Schedule Saver" thread) eventually comes around and updates
sysjobschedules
; from my initial investigation it appears this is every 20 minutes, and only happens ifxp_sqlagent_notify
has been called due to a change made to a job since the last time it ran (I did not perform any further testing to see what happens if one job has been changed and another has been run, if the "Schedule Saver" thread updates both - I suspect it must, but will leave that as an exercise to the reader).I am not sure if the 20-minute cycle is offset from when SQL Agent starts, or from midnight, or from something machine-specific. On two different instances on the same physical server, the "Schedule Saver" thread updated
sysjobschedules
, on both instances, at almost the exact same time - 18:31:37 & 18:51:37 on one, and 18:31:39 & 18:51:39 on the other. I did not start SQL Server Agent at the same time on these servers, but there is a remote possibility that the start times happened to be 20 minutes offset. I doubt it, but I don't have time right now to confirm by restarting Agent on one of them and waiting for more updates to happen.I know who did it, and when it happened, because I placed a trigger there and captured it, in case I couldn't find it in the trace, or I inadvertently filtered it out.
That said, it is not hard to catch with a standard trace, this one even comes through as non-dynamic DML:
If you want to run a more filtered trace to track this behavior over time (e.g. persisting through SQL Agent restarts instead of on-demand), you can run one that has appname =
'SQLAgent - Schedule Saver'
...So I think that if you want to know the next run time immediately, look at
sysjobactivity
, notsysjobschedules
. This table is directly updated by Agent or its background threads ("Update job activity", "Job Manager" and "Job invocation engine") as activity happens or as it is notified byxp_sqlagent_notify
.Be aware, though, that it is very easy to muck up either table - since there are no protections against deleting data from these tables. (So if you decided to clean up, for example, you can easily remove all the rows for that job from the activity table.) In this case I'm not exactly sure how SQL Server Agent gets or saves the next run date. Perhaps worthy of more investigation at a later date when I have some free time...