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":
After that, it is possible to receive the following error message:
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":
After clicking "Next", the following page will appear:
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:
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:
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":
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:
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":
There is an option to store the report in a text file and also E-mail the report.
After clicking "Next" and then "Finish" our maintenance plan will be created:
We can find it under "Management"> "Maintenance Plans":
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:
This is because our SQL Server Agent service is not running:
In SQL Server Configuration Manager, we can see that it is disabled:
For this demo, we will choose "Manual" and then right clicked on it and start it:
It is possible to start SQL Server Agent from SSMS as well, by right clicking on SQL Server Agent and selecting Start.:
Now, when SQL Server Agent is started, we can successfully execute the maintenance plan:
Under SQL Server Agent jobs, we can see the associated job’s history by right-clicking on it:
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:
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:
- SQL Server Maintenance Tips
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/create-a-maintenance-plan?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/agent-xps-server-configuration-option?view=sql-server-2017
