Contents
How often does SQL Agent update next run date?
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.
What happens if next run date is not updated?
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:
How to calculate the next run date in SQL?
to get the next run date for a job you can use then sysschedules and sysjobschedules tables. check the next_run_date and next_runtime columns from the table sysjobschedules. next_run_date int Next date on which the job is scheduled to run.
How does next run date and next run time work?
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.
How to know next run time in SQL Server?
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.
When does MSDB update next scheduled run date?
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.