mssqltips logo

Create a New SQL Server Maintenance Plan with the Maintenance Plan Designer

By:   |   Updated: 2019-09-30   |   Comments (2)   |   Related: More > Maintenance

Problem

We are continuing the series of articles devoted to SQL Server Maintenance Plans. In the previous article, we discussed the SQL Server Maintenance Plans Wizard and illustrated how to configure and design a sample Maintenance Plan by using the Wizard. In order to create some special tasks, however, much more flexibility is needed than Maintenance Plan Wizard can provide. In these cases, Maintenance Plan Designer can be very helpful as it possesses more features for designing a maintenance plan as compared to the Wizard.

Solution

In this article, we will explore the Maintenance Plan Designer. The advantages of the Maintenance Plan Designer over the Maintenance Plan Wizard will be discussed and a sample maintenance task will be created by using the Designer.

Introducing the SQL Server Maintenance Plan Designer

To start the Maintenance Plan Designer, we can perform a right-click on "Maintenance Plans" under "Management" in SQL Server Management Studio and choose "New Maintenance Plan…":

new maintenance plan

Then, we should type the name of a new maintenance plan. In the example below, we have typed "MaintenancePlan2" as the name of our new maintenance plan:

new maintenance plan

After clicking "OK", the Maintenance Plan Designer will open:

maintenance plan designer

SQL Server Maintenance Plan Designer Toolbox

On the left side, we can see the Toolbox, where maintenance plan tasks are included. In case the Toolbox does not show, it can be displayed from "View" > "Toolbox" menu or by using the shortcut "Ctrl+Alt+X":

Maintenance Plan Designer Toolbox

It is also possible to choose a specific task from the Toolbox by right-clicking on the Toolbox and choosing "Choose Items":

Maintenance Plan Designer Toolbox

Then, some tasks, which are not necessary during a specific project, can be excluded by unchecking them from the list:

Maintenance Plan Designer Toolbox options

In this case, after clicking "OK", we will have only two tasks in the Toolbox:

Maintenance Plan Designer Toolbox

To bring the Toolbox to its default state, select "Reset Toolbox":

Maintenance Plan Designer Toolbox reset

SQL Server Maintenance Plan Designer Example

On the right, we can see the buttons for managing subplans, connections and so on. Subplans allow us to schedule maintenance plan tasks, unlike with the Maintenance Plan Wizard, where each task is automatically assigned to its own subplan.  With the Maintenance Plan Designer, it is possible to create more than one subplan and each subplan can be assigned to more than one task. On the right side, under the subplans, is an area for designing maintenance tasks, which is called the Design Surface. To design a task, we can just drag and drop a task from the Toolbox to the Design Surface. In this article, like in the previous one, let's drag and drop "Check Database Integrity Task" from the Toolbox to the Design Surface:

Maintenance Plan Designer example

This task runs a DBCC CHECKDB command for all objects in the specified database(s) in order to perform physical and logical integrity checking. We can see from the picture that the "No connection manager is specified". To specify a connection, we should double-click on the task and select the appropriate connection. In our example, we have chosen only one database for integrity checking:

Maintenance Plan Designer example

After clicking "OK", a new window appears where we can choose some options for checking database integrity:

Maintenance Plan Designer example

Let's click "OK" and continue. We can change the name of the task by right-clicking on it and choosing "Rename":

Maintenance Plan Designer example

After changing the name of the task to "Check ABC_TestDB Database Integrity" we will set the schedule for the plan by clicking on the "Schedule" under the "Subplan" and choose or setup the preferred schedule:

Maintenance Plan Designer example

Now, we will click on the "Save" button in SSMS and the plan is ready. It can be found and executed in SSMS under "Management" > "Maintenance Plans":

Maintenance Plan Designer example

After clicking "Execute", we can see the progress and that the plan successfully completed:

Maintenance Plan Designer example

Features Specific to the SQL Server Maintenance Plan Designer

Now, having demonstrated this sample task, let's review some features of Maintenance Plan Designer which are not available in Maintenance Plan Wizard.

First of all, unlike the Maintenance Plan Wizard, it is possible to see the T-SQL code of the task in the Designer. To do so, click on "View T-SQL":

Maintenance Plan Designer other features

This code can be copied and used manually, if needed.

In the tasks' list of the Designer we can see that there are two tasks which are not included in the tasks' list of the Wizard:

Maintenance Plan Designer other features

Those are "Execute T-SQL Statement Task" and "Notify Operator Task" tasks:

Maintenance Plan Designer other features

The first one allows us to include custom Transact-SQL code into the maintenance plan. In the example below, we are inserting a row into a table by using manually written T-SQL code:

Maintenance Plan Designer other features

The "Notify Operator Task" task allows us to receive notifications in case of failures in the Maintenance Plan execution. To be able to create this task, there should be Operators with email addresses on the server.  Also, database mail needs to be configured.

Maintenance Plan Designer other features

While the Maintenance Plan Wizard allows us to include only one specific task in a plan, in the Designer it is possible to add the same task into the plan more than once. For instance, we can add one more "Check Database Integrity Task" into our "MaintenancePlan2":

Maintenance Plan Designer other features

Please be advised that this example is only for illustrating the idea of adding the same task into the plan more than once. Usually, in such cases, it is preferable to have just one "Check Database Integrity Task" task and choose those databases which should be checked.

Subplans in the Maintenance Plan Designer also adds a significant degree of flexibility to maintenance plans.  This is because subplans can include more than one task and, therefore, multiple tasks can be executed together according to a single schedule. In contrast, in case of the Maintenance Plan Wizard, schedules are assigned to tasks and each task runs based on its schedule. Additionally, it is possible to create more than one subplan in the Designer. In this way, we can have several groups of tasks included in one maintenance plan and each group of tasks can run based on its own schedule. To illustrate this, let's add a new subplan to our "MaintenancePlan2" by clicking on "Add Subplan" in the top-left corner of the menu:

Maintenance Plan Designer subplans

We have added a new task to Subplan2. Now, we have two groups of tasks in a single maintenance plan. Both run on a weekly basis – on Sundays, the other on Mondays. If we open SQL Server Agent > Jobs, we can see that there is a separate job for each subplan and each of them has its own schedule:

Maintenance Plan Designer schedules

The Maintenance Plan Designer allows us to develop conditional logic between tasks. In other words, it is possible to design the plan in such way where the execution of the task will be based on the outcome of the previous task. In the next example, we will add a simple logic between executions of the tasks in the MaintenancePlan2.Subplan_1. This will be done by using "Precedence Constraint" which is the green arrow in the designer. We will direct that arrow from the "Check ABC_TestDB Database Integrity" to "Check New_TestDB Database" task:

Maintenance Plan Designer precendence constraints

After double-clicking on the constraint, we can edit it and define the logic. In our example, we have defined the simplest logic – the second task will run only if the first one successfully completes. However, the Designer allows us to develop much more complicated logic by using not only constraints, but also other evaluation operations, such as expressions with various functions:

Maintenance Plan Designer expressions

Hence, defining conditional logic between tasks will definitely make the development of maintenance plans much more flexible and will allow you to solve complicated maintenance tasks by using Maintenance Plans.

Conclusion

In conclusion, the Maintenance Plan Designer is a useful tool for designing and developing maintenance plans. While the Maintenance Plan Wizard has some limitations in designing maintenance plans, the Maintenance Plan Designer adds much more flexibility to this process and allows you to design complicated maintenance solutions.

Next Steps

To find additional information about the discussed topic please follow the links below:



Last Updated: 2019-09-30


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, October 02, 2019 - 10:44:31 AM - Sergey Gigoyan Back To Top

Dmitry, thank you for the question.

SQL Server Integration Services can be used to import and export maintenance plans' packages. In the upcoming articles, this will also be discussed.

Thanks,
Sergey


Tuesday, October 01, 2019 - 11:55:32 AM - Dmitry Vakhrushev Back To Top

Sergey, thank you for the article. How will you script those plans out and save to the source control? Is there an easy way to copy the exact same plan with a different name or to a different server?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools