Tip: Never disable a job temporarily

Shortcuts & tips from an Impatient DBA #6

This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are the little things that I do as part of my daily work that make me more efficient.

Never disable a SQL Agent job temporarily

Sometimes I’m a forgetful DBA in addition to being an impatient one. This tip is less about efficiency and more about having a safety net.

Every once in a while when you’re doing maintenance, testing, debugging, problem-solving, or <whatever> you tell yourself, “I need to disable this job to make sure it doesn’t run in the next 10 minutes.” Right there, you’re setting yourself up for disaster. One of the few guarantees in Database-Land is that there will be distractions–someone or something will steal your attention before you’re done with what you’re working on.

What happens if you never remember to re-enable that disabled job? How long will it go unnoticed? How big of a problem will it cause? I accidentally left a transaction log backup job disabled in Production once–I noticed when the drive filled up and things ground to a halt. Oops.

You should probably have some sort of monitoring for when jobs are disabled–but that’s a whole other post.

The answer is so obvious, it took me years to think of it: Just change the start date to tomorrow. No, it’s not perfect–far from it. But at least I have a safety net!  The job won’t be disabled forever. Going without transaction log backups for 12 hours could be a huge problem: I could still fill a drive, I’m not meeting my RPO, I’m affecting log shipping…

Not all jobs are that critical (or run so frequently). For most of my jobs, resuming the normal schedule at midnight would save me getting paged at 3am.

What I’m trying to say is: Everyone screws up eventually, so do everything you can to make your failures hurt less. 

1 Comment

  1. Nice advice but I go a bit further and note down all my changes and for things like this mark it in a calendar with reminder as well. Part of it is to satisfy audit, my boss knows I will go out of my way to reduce his annual headaches with audit reporting, especially with SOX systems. One of the benefits is that any soul-draining jobs from audit that get farmed out to my area will go to me last.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.