Create SQL Server Maintenance Plan with Maintenance Plan Designer

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…”:

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:

After clicking “OK”, the Maintenance Plan Designer will open:

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

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

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:

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

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:

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:

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

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

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:

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

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

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:

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

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:

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.

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

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:

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:

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:

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:

Resources

Community

Subscribe