Yes, you make a good point. I foresee 3 scenarios that I'd like to cater for:
-
If we are going to have a possible period of instability (after upgrading / doing "something") I might want to disable all jobs EXCEPT backups - so that they keep running (as they can safely be interrupted). This would be as an alternative to "Disable SQL Agent"
-
If we have been doing something which has prevented overnight jobs running I might want JUST THEM to be disabled until "following night", rather than cut in "as soon as the server is powered back up"
-
Last thing that I can think of is to disable some/all jobs, then make sure they are NOT still running, and only then allow IT to reboot the server. My understanding is that stopping SQL Agent Service will immediately kill (ungracefully) anything that is running Command Prompt type stuff, which is a lot more ugly than I would like.
Our stuff is, mostly, out on Client servers and their IT people talk to us far less than they should, and have no understanding of SQL at anything other than a superficial level, so whilst I may well be over thinking the problem - something I'm definitely inclined to do! - providing some simple "Tick this" options in our APP that will provide 80:20 solutions is way better than what we have right now - which is "We powered the server down and all sorts of goofy stuff is now happening" along with an implied "presume this is covered by our fixed price maintenance contract" ...
I've done the "Disable Jobs in this Name List" before (although I prefer Job ID GUIDs as names tend to be a bit fluid when we don't own the server!), but I also want to consider whether any new job, that has been added (i.e. which might is not, yet, in the List), needs to be included and I think this approach will help.
My new "Step 1 - Check if not currently Active" is, as a useful side effect, adding any new jobs to my table - so I get the opportunity to consider newly added jobs and categorise them as appropriate - e.g.
- All jobs
- Non-Backup Jobs
- Overnight jobs
- APP specific jobs
which can then be used as the basis for disabling specific collections of Jobs. This has nothing to do with the ENABLE / DISABLE property on the Job itself, so no chance that anything disabled in that way gets accidentally re-enabled.