Short Answer
It looks like the data in msdb.dbo.sysjobschedules
is updated by a background thread in SQL Agent, identified as SQLAgent - Schedule Saver
, every 20 minutes (or less frequently, if xp_sqlagent_notify
has not been called and no jobs have run in the meantime).
For more accurate information, look at next_scheduled_run_date
in msdb.dbo.sysjobactivity
. This is updated in real-time any time a job is changed or a job has run. As an added bonus, the sysjobactivity
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:
It could be up to 20 minutes before sysjobschedules reflects the truth; however, sysjobactivity will always be up to date. If you want a lot more details about this, or how I figured it out...
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:
msdb.dbo.sp_add_jobschedule == calls ==> msdb.dbo.sp_add_schedule
msdb.dbo.sp_attach_schedule
msdb.dbo.sp_attach_schedule == calls ==> msdb.dbo.sp_sqlagent_notify
msdb.dbo.sp_sqlagent_notify == calls ==> msdb.dbo.xp_sqlagent_notify
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:
exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME
SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2)
UPDATE msdb.dbo.sysjobactivity
SET next_scheduled_run_date = @nextScheduledRunDate
WHERE session_id = @P3 AND job_id = @P4',
N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',
20120819,181600,5,'36924B24-9706-4FD7-8B3A-1F9F0BECB52C'
It seems that sysjobactivity
is updated immediately, and sysjobschedules
is only updated on a schedule. If we change the new schedule to be once a day, e.g.
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
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.:
SQLAgent - Job Manager
SQLAgent - Update job activity
SQLAgent - Job invocation engine
SQLAgent - Schedule Saver
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 if xp_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.
CREATE TABLE dbo.JobAudit
(
[action] CHAR(1),
[table] CHAR(1),
hostname SYSNAME NOT NULL DEFAULT HOST_NAME(),
appname SYSNAME NOT NULL DEFAULT PROGRAM_NAME(),
dt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);
CREATE TRIGGER dbo.schedule1 ON dbo.sysjobactivity FOR INSERT
AS
INSERT dbo.JobAudit([action], [table] SELECT 'I', 'A';
GO
CREATE TRIGGER dbo.schedule2 ON dbo.sysjobactivity FOR UPDATE
AS
INSERT dbo.JobAudit([action], [table] SELECT 'U', 'A';
GO
CREATE TRIGGER dbo.schedule3 ON dbo.sysjobschedules FOR INSERT
AS
INSERT dbo.JobAudit([action], [table] SELECT 'I', 'S';
GO
CREATE TRIGGER dbo.schedule4 ON dbo.sysjobschedules FOR UPDATE
AS
INSERT dbo.JobAudit([action], [table] SELECT 'U', 'S';
GO
That said, it is not hard to catch with a standard trace, this one even comes through as non-dynamic DML:
UPDATE msdb.dbo.sysjobschedules
SET next_run_date = 20120817,
next_run_time = 20000
WHERE (job_id = 0xB87B329BFBF7BA40B30D9B27E0B120DE
and schedule_id = 8)
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
, not sysjobschedules
. 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 by xp_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...
In the dbo.sysschedules table you will see all of the data needed to calculate the schedules.
The columns named active_*
delimit the periods in which the jobs can run.
The columns named freq_*
define the frequency of the job runs. You will notice a freq_type
, which defines a type of frequency, which drives how the freq_interval
and other columns are interpreted.
Please read the definitions at:
http://msdn.microsoft.com/en-us/library/ms178644.aspx
EDIT: You can calculate the next_run_datetime by using one of the following:
sp_help_jobschedule @job_name = 'Jobname'
sp_help_jobschedule @job_id= '<job id guid>'
Since there can be several schedules you can also use the schedule_id as such:
sp_help_jobschedule @schedule_id = 1, @job_name = 'Jobname'
This will return a data set in which the last columns give the data, such as:
next_run_date next_run_time schedule_uid job_count
20141110 190000 301DC5D5-9569-49FA-8BC5-9129109AFA6A 1
And you can script out the msdb procedure sp_help_jobschedule
to see the code in the procedure, in case you want to include some of that in your own script.
Best Answer
You can use the (documented) stored procedure
sp_help_job
to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nestedINSERT...EXEC
calls. As an alternative, many folks use the (undocumented) stored procedurexp_sqlagent_enum_jobs
which captures much of the same info.Like you, I prefer to capture the data from the base tables directly using an alternate approach that I wrote about in a blog post, and have published a T-SQL function on GitHub as part of my DBA Database.
The table-valued function (TVF) definition itself is just a single SELECT statement that takes a job name, and returns a bunch of stuff, including last & next run time, and whether the job is currently running:
Because it’s a TVF, you can pass a value directly to it and get a result set back:
Or APPLY it across a table for a set of results (in this case I’m getting all job in job_category 10):
Or get a list of all SQL Agent jobs that are currently running:
What about
sysschedules
?You'll notice that my code doesn't use
sysschedules
, because it just isn't necessary.If you wanted to make use of
sysschedules
in code, you would do something like this, joining fromsysjobs
tosysjobschedules
tosysschedules
. Keep in mind that jobs can have zero to many schedules.To determine the "next run date/time" you would need to essentially identify active schedules for the job, and take the next run date out of multiple schedules.
sysjobactivity
does that heavy lifting for you by computing the next run date (you can think of it as the "actual next execution")--but it simply doesn't keep track of which schedule is responsible for that execution. If that matters to you, you'll need to hunt that down yourself by looking at what schedules are active for that job.