Problem

Creating SQL Server database maintenance solutions are essential in terms of having a well-organized, safe, and high-performance database environment. This can be achieved by using SQL Server maintenance plans which allow easy creation and scheduling of common database maintenance tasks such as performing backups, checking database integrity, maintaining indexes, updating statistics and so on. This facilitates the implementation of routine database tasks. This series of articles are devoted to SQL Server maintenance plans, new features, usage, design, and development. Many of these tasks will be discussed in separate articles and will be illustrated by practical examples.

Solution

This article is mainly devoted to the SQL Server Maintenance Plan Wizard. Firstly, we will introduce maintenance plans, their features and usage. Then, describe how to use the Maintenance Plan Wizard and illustrate how to create a maintenance plan by using it.

Introducing SQL Server Maintenance Plans

Actually, when creating a maintenance plan, SQL Server Management Studio (SSMS) creates an SSIS (SQL Server Integration Service) package and the package is run by a SQL Server Agent Job.

Maintenance plans can be created from SSMS as well as using T-SQL code. In older versions of SQL Server, maintenance plans have some limitations. If there is a need to create flexible database maintenance solutions, it would be better to do it manually, using T-SQL code, instead of using maintenance plans. In newer versions of SQL Server, the majority of these limitations have been eliminated and maintenance plans have become more flexible and useful. There are two possible ways to create maintenance plans from SSMS – the Maintenance Plan Wizard and the Maintenance Plan Designer. Let’s first start with the Maintenance Plan Wizard.

SQL Server Maintenance Plan Wizard

The Maintenance Plan Wizard provides a user-friendly interface for creating database core maintenance tasks. Therefore, it is possible to create maintenance tasks quite easily using this tool.

To do so, in SSMS we need to right click on "Maintenance Plans" under "Management" and then choose ‘’Maintenance Plan Wizard":

policy management

After that, it is possible to receive the following error message:

microsoft sql server management studio

This means that SQL Server Agent extended stored procedures component is not enabled on the server. Without enabling it, the SQL Server Agent node will not be visible in SSMS. You can use the following code to enable the "Agent XPS" component:

SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'AGENT XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO            

After running this code, we can successfully start the "Maintenance Plan Wizard":

sql server maintenance plan

After clicking "Next", the following page will appear:

maintenance plan wizard

Here we can choose a name for the maintenance plan, choose the account under which it will be run. Maintenance plan tasks can be scheduled in order to run periodically or can be left without a schedule and run manually when needed. Note, that it is possible to choose one schedule for all maintenance tasks included in the plan as well as a separate schedule for each of them. You can set the schedule by clicking the "Change" button:

new job schedule

On the page above, there are various options for configuring the schedule. After scheduling and clicking "Next" we will see core database maintenance tasks. As we can see, there are 11 tasks and it is possible to include more than one task in a plan. To demonstrate, let’s choose the first – "Check Database Integrity" task:

select maintenance tasks

This task performs physical and logical integrity checking for all objects in the specified database(s) by running DBCC CHECKDB command. It is an important task in terms of making sure that the database is consistent and is not damaged. We will continue by clicking "Next":

maintenance task order

As there is only one task, the buttons for arranging task order is not active. After clicking "Next" again, the screen for choosing databases will appear:

maintenance plan wizard

Hence, it is possible to set the task for all databases, only for the system databases or for specific databases. Additionally, it is there is a checkbox for ignoring databases that are not online. We choose only one database and then clicked "OK" then "Next":

select report options

There is an option to store the report in a text file and also E-mail the report.

complete the wizard

After clicking "Next" and then "Finish" our maintenance plan will be created:

maintenance plan wizard

We can find it under "Management"> "Maintenance Plans":

maintenance plan

In addition to that, the corresponding job has been created under "SQL Server Agent" > "Jobs". As our maintenance plan is not scheduled, we will run it by right clicking on it and clicking "Execute". In our case, we will receive an error:

execute maintenance plan

This is because our SQL Server Agent service is not running:

execute maintenance plan

In SQL Server Configuration Manager, we can see that it is disabled:

sql server agent

For this demo, we will choose "Manual" and then right clicked on it and start it:

properties

It is possible to start SQL Server Agent from SSMS as well, by right clicking on SQL Server Agent and selecting Start.:

sql server

Now, when SQL Server Agent is started, we can successfully execute the maintenance plan:

execute maintenance plan

Under SQL Server Agent jobs, we can see the associated job’s history by right-clicking on it:

sql server agent

This information can also be seen by right clicking on the maintenance plan and choosing "View History".

In order to edit an existing maintenance plan, we can either double click on it or right click and choose "Modify". In this case, instead of the Maintenance Plan Wizard, the Maintenance Plan Designer will be opened:

maintenance plan

The Maintenance Plan Designer provides much more features and flexibility than the Maintenance Plan Wizard. Thus, after creating a basic maintenance plan using the Wizard, we can edit it and add new tasks and enhance the functionality with the Designer.

The Maintenance Plan Designer will be discussed in detail in the next article of this series.

Conclusion

To sum up, designing maintenance plans appropriately could play a major role in developing database maintenance solutions. The Maintenance Plan Wizard could be used to easily create a maintenance plan. Although this wizard only allows the creation of core maintenance tasks, these tasks can be edited and enhanced after creation by using the Maintenance Plan Designer.

Next Steps

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