[EN] How to DO IT? My Maintenance SUB-Plans doesn’t work.

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:

image

Pict. 01 | Error with execution of Maintenance Plans.

When we look into message error we get more details:

image

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:

Captura_MaintenancePlans2005_08

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:

Captura_MaintenancePlans2005_09

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:

image

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:

image

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):

image

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).