SQL Server Maintenance Plans (Part 1)
18 October 2012, by Mike McLean
I recently had to overhaul the Backups System for one of our customers using SQL Server 2005 Maintenance Plans, and my main take away is that it’s a pain in the rump. It’s a fairly frustrating interface in general, and there are a couple of particular gotchas that I thought I should share with anyone who ends up doing the same sort of thing.
The GUI is not a flow chart, despite appearances
SQL Server allows you to chain tasks together to alter the flow based on the outcome of the events; Green for success, red for failure.
Now, it might look as though this says “Run A then, if success Run B, if failure Run C.” And indeed, if you execute this you’ll get either A & B, or A & C, depending on what happened to A. But that doesn’t appear to be the exact logic. It actually seems to do the following:
- Run A.
- Run B after A, as long as A did not fail.
- Run C after A, as long as A did fail.
In other words, these diagrams are not Flow Charts, they are Precedence Tables – they indicate dependencies not consequences.
If you want to prevent anything from running, disable ALL the tasks
You might think there’s not much difference between Flow Charts and Precedence tables, but consider the following case:
Here the source task is disabled (Greyed out). So one might expect that nothing executes … In fact, B is run, because the first task is not running, and did not fail.
If you want to reach a task from multiple locations, set the precedence constraints to ‘OR’ mode
See this attempt at standard error reporting:
The designer is surely trying to write “Do these tasks and notify me if any fail”. i.e.
- Run X.
- If X Succeeds, Run Y.
- If either X or Y Fail, Run Z.
Alas, not. Z is dependent on both X and Y failing. If X fails, then Y doesn’t run(as expected). But if Y doesn’t run, then it can’t fail, so Z doesn’t run either!
The solution is in the constraint editor, in an option that you might easily not notice, since the majority of the plan can be created without entering this editor at all.
Switching to “OR” mode will result in Z running if any of its dependencies Fail (or a success constraint running if any of its dependencies succeed). It also adjusts the GUI:
Once the system has been understood, the rules are reasonably easy to follow, but until then it can lead to some incredibly confusing situations, as it’s more natural to assume that there’s a mistake in one of the tasks being run, than to consider that your mental model of the system is flawed, especially when that system appears to be such a familiar one!
So that’s the Flow Control system sorted, now we just have to select the actions that get performed at each stage which should be straight forward … or not, as the case may be. Next time I’ll tell you about the joys of running Agent Jobs as part of Maintenance Plans.