Introduction
Due to the large number of different SQL Server instances (and versions) we support, we get the luxury of seeing unusual behaviour more often than most. Something I’ve seen three times now is a curious issue with the SQL Server Agent on SQL 2016 CU5 which rarely crops up following a restart of the server.
The Story
We were alerted out of hours to log backups not having been taken on an instance, so connected to investigate and found that all the Agent Jobs were marked in the “Next Run” field as “Not Scheduled” despite this not being the case. Opening up the job properties proved that they were indeed scheduled to occur, however the agent didn't recognise this and wasn’t executing jobs.
(example image)
How to resolve
In the cases we encountered, a quick restart of the SQL Server Agent fixed this, jobs began running on schedule again, and Activity Monitor displayed the appropriate next run time.
What strikes me about easily resolved issues such as the above is how important reliable monitoring is. "A problem known is half solved", and thanks to our alert we quickly resolved the issue and let the customer know. Had such monitoring not been in place, not only would any scheduled processes not have run (be that payroll, reporting or bespoke business logic) but log and full backups wouldn't have been taken.
While we've only seen the above on IaaS Virtual Machines running SQL 2016 CU5 it's not to say that other versions aren't also affected. Let us know in the comments if you've seen this on different versions!