Last night I discovered on one of my customers systems, that Maintenance Plan doesn’t work. After few hours I received request form customer with printscreen:
Pict. 01 | Error with execution of Maintenance Plans.
When we look into message error we get more details:
Pict. 02 | Weekly Batch contains multiple plans…
But what is wrong with that? Customer’s idea was: to executing one batch (in this case weekly) with four subplans such: DBCC CHECKDB, REORGANIZE INDEXES, UPDATE STATISTICS and BACKUP (type FULL) for All Systems Databases. All together looks like this:
Pict. 03 | One batch, four subplans and errors
The administrator creates exactly the same weekly batch for Users Databases, but it’s one small and important difference:
Pict. 04 | Executing “exactly” the same batch for All User Databases.
The difference is very clear: they are four plans in first example and one in second. Let’s comparing differences:
Pict. 05 | I’ve got You! Right side: System Databases and Leftt side: User Databases.
As we see our maintenance plans are different, and they cannot work the same. Now we have two options: we can change first batch in SSIS or creat the new one (simple and fast option). But be sure that we will not repeat the design error:
Pict. 06 | We must remember about second option: Single schedule for ENTIRE PLAN or no schedule
At the end we have two Maintenance Plans, designed and scheduler very similar (only schedule od execution and types od databases are different):
Pict. 07 | Comparing the batches.
Think twice (what you need), design (how it shoud work), think one more (how it looks) time and execute (to check).