Home / server / Questions / 132420
Accepted
Adam J.R. Erickson
Asked: 2010-04-15 10:44:21 +0800 CST

Can you make a SQL Server Maintenance Subplan run after a previous subplan completes

  • 772

I have a maintenance plan to do backups and cleanup of old data. It has three subplans, each with their own schedule. Can I set the schedule of subtask 2 to be "after subtask 1 is complete"?

sql-server sql-server-2005 backup ssms maintenance

1 Answers

  1. Best Answer

    Yes you should be able to but in a slightly roundabout way. If you go to your SQL Server Agent and view the jobs you'll notice that these are all the pieces of your maintenance plan all broken up into separate tasks. Double click on one and then go to the Steps section. Notice how it says that On Success it says Quit the Job Reporting Success? This can be changed to either quit reporting success, quit reporting failure, or go to the next step.

    Now back in the Steps section of Job Properties you should be able to add a new step. The Type for the new step is probably set to T-SQL script by default and this is what we want. All of the maintenance plan options you set are actually just running T-SQL to accomplish the jobs. You can even view the T-SQL they're using within the MaintenancePlan designer by double clicking on a task then clicking View T-SQL.

    So now you can see that the maintenance plan is really only creating separate SQL Server Agent jobs and running T-SQL scripts (in the form of Integration Service Packages when using the maintenance plan tool) to do them. If you can come up with your own T-SQL scripts to do backups and cleanup you can then schedule them under the same job and simply tell each job to move to the next step upon completion.

    I don't know of a way to do this directly from the MaintenancePlan designer as all it seems to have is that little calendar where you can set the time for the job to run but with a little work you can get a backup plan running the way you want it to. Creating a custom job this way may end up being slightly different than going through the designer since you're not making use of integration services but it is still very capable.

    Edit:

    Actually come to think of it you could replicate this within the designer if you wanted. Instead of creating extra subplans you could simply combine all 3 of your subplans into one large subplan. This way they would all run under the same SQL Server Agent job and you could set them up to run one after another. Again it's a bit of a roundabout way to do it but each time you create a new subplan it treats it as a brand new job which needs to be scheduled separately.

    • 1