We have had an issue, from time to time, where Clients' 3rd Party IT support has rebooted server and it interfered with Scheduled Jobs.
Most recent one was:
Client: "We have scheduled powercut on Wednesday, we are going to powerdown at 6PM, get (rented) generator running at 9AM, power up, then power down when the power comes back on, turn off generator and switch back to mains power, then power up again. Any problem with your APP?"
We said that was fine because the only jobs that might be running at 6PM and "when power comes back on" would be Transaction Backups, and their power-fail-recovery would be fine. When the server was powered up at 9AM (on generator) all sorts of overnight jobs would run (having been delayed from "middle of night"), but that would be fine too.
But what actually happened was that they had a problem with generator, and rebooted / power-cycled the server 3 times within a few minutes at 9AM. Overnight jobs had already kicked in and some only half-ran, which caused a variety of problems.
If we are expecting multiple reboots - e.g. when installing upgrades / patches that will require several reboots -
we disable SQL Server Service Auto-Start. In other circumstances, where users could be using SQL during the maintenance, we disable SQL Jobs. But that's a bit of a pain, plus Client has to involve us ...
What I'm thinking is to have every SQL Agent Job (which we "own") check some sort of "Global Flag" and not run if that is disabled. Maybe the flag could be the Date/Time of a "DO not run until AFTER" value. Client could set that flag/value themselves.
Does anyone do this? Do you use an alternative method? Any Gotchas?
One risk I perceive is that the SQL Agent jobs never get started again! so I'd want to have some sort of alert that covers that. We have a Banner Warning in our APP - for announcements like "Scheduled maintenance at 4PM, please complete your work before then" ... so that could include "Scheduled tasks are overdue, please inform Admin" as that would run without any scheduled task / notification system needing to run at all.