I've been dealing with the Microsoft SQL Server Agent with regards to scheduling jobs.
I'm trying to find out what the next scheduled run date for a bunch of jobs that were created where the job ids from the msdb sysjobs table(s) joins back to my specific system tables.
We store the job ids that get created in sql server agent in our own system table in order to join back.
I've now discovered the sysjobsactivity table. It depends on a session. I may have a job that had a next scheduled run date in an earlier session, but a NULL value for that field in the latest session.
Restarting sql agent is at least one way to create a new session id. It looks like all existing jobs get an entry associated to the new session but the next scheduled run date doesn't necessarily move forward.
I'm pretty sure if I update a job with criteria that places all occurrences in the past, the next run date will be NULL, which makes sense.
But I do see cases where I have a recurring schedule with no end date, and the next scheduled run is NULL which seems wrong.
It's also strange that some of the values in msdb.dbo.sysjobschedules.next_run_date and msdb.dbo.sysjobschedules.next_run_time differ from the value stored in msdb.dbo.sysjobactivity.next_scheduled_run_date.
The sysjobschedules tables is saying the next run date is the occurrence after the value claimed as the next run date in the sysjobactivity table.
You'd think those would get calculated the same way and would be equal.
How do I correct this?