Creating Database Maintenance Plans

Using database maintenance plans, we can automate basic database administration activities. Maintenance plans are SQL Server integration service packages which are executed by SQL Server Agent job on configured schedule. Using maintenance plans, we can automate the following tasks.

1. Shrink a database
2. Backup a database
3. Operator notification
4. Update database statistics
5. Perform database integrity check
6. Execute SQL Server Agent jobs
7. Execute T-SQL script
8. Reorganize or rebuild indexes

Moreover, we can set up an alert to notify a DBA about the status of maintenance plan execution by enabling the Database Mail.

In this article, I am going to explain how to create a basic maintenance plan to perform database consistency check on a user database.

Pre-requisites to create a database maintenance plan

To create a database maintenance plan, the pre-requisites are the following:

1. Integration services must be installed
2. SQL Server Agent Service must be running.

Integration services must be running

As I mentioned, database maintenance plans are SQL Server integration service packages, hence to execute them, integration services must be installed.

To verify that the integration services have been installed, open SQL Server Configuration Manager, select SQL Server Services in the left pane. You can view the integration service version on top of the list.

If the integration services were not installed, then install them using the SQL Server installer. See this article for reference.

SQL Server Agent Service must be running

Database Mail sends emails or messages using the SQL Server Agent Service. If the Agent Services are not running, then SQL Server cannot send emails. While the Agent Service is not running, all the messages will be queued, and when the Agent service starts, all the queued messages will be delivered.

To verify the status of the Agent Service, execute the following command in SQL Server Management Studio.

use master
go
EXEC xp_servicecontrol N'querystate',N'SQLAgent$CMS'

The output is as follows:

If the SQL Server Agent Service is not running, then we must start it. To start the SQL Server Agent Service, open services, go to Control Panel, click Administration Tools, and then click Services. In the list of services, search for SQL Server Agent. Right-click SQL Server Agent and select Start.

If you want to email the status of the maintenance plan than you must enable the Database Mail and it can be done by changing the database instance configuration parameters. By default, the Database Mail feature is not enabled. It must be enabled by changing the configuration value of Database Mail XPs.

To enable Database Mail, execute the following queries in sequence.

USE master
Go
EXEC sp_configure 'show advanced options', 1 --Enable advance option
Go
RECONFIGURE
Go
EXEC sp_configure 'Database Mail XPs,' 1 --Enable database Mail option
Go
RECONFIGURE
Go
EXEC sp_configure 'show advanced options', 0 --Disabled advanced option
Go
RECONFIGURE
Go

Demo Setup

For this demo, I will create a maintenance plan to perform database consistency check on the FileStream_Demo database. The maintenance plan will execute every Sunday at 12:00 AM.

SQL Server version: SQL Server 2017.

Software: SQL Server Management Studio.

A database maintenance plan can be created either using the Maintenance Plan Wizard or using maintenance plan design surface. Firstly, I will explain how to create the maintenance plan using the Maintenance Plan Wizard.

Create a maintenance plan using Maintenance Plan Wizard

We can create multiple maintenance plans using the Database Maintenance Plan Wizard. The wizard has some predefined maintenance tasks which can be configured step by step.

To create a maintenance plan using the wizard, open SSMS, expand Management, right-click Maintenance Plan and select Maintenance Plan Wizard.

The Maintenance Plan Wizard dialog box opens, where you can see the summery of tasks that can be performed using the wizard. You can ignore it and click Next.

On the next dialog, specify the desired name and description of the maintenance plan. Specify the user or account which executes the maintenance plan. As I mentioned, you can create multiple maintenance plans using the wizard; this dialog contains an option to configure the schedule. You can execute all tasks at the same time, or you can specify a single schedule for the entire plan. Specify the desired name and description in the Name and Description text boxes. SQL Server Agent runs the maintenance plan if SQL Server Agent service account is selected. Our plan will execute individually, thus select Single schedule for the entire plan or no schedule.

To configure the schedule, click the Schedule button next to the Change text box. The New Job Schedule dialog box opens. Specify the desired name in the Name text box. The maintenance plan will execute weekly, hence select Recurring in the Schedule type drop-down box. As mentioned, the maintenance plan executes weekly, hence select Weekly in the drop-down box and check Sunday. The task executes at 12:00 AM, hence enter 12:00:00 AM in the corresponding text box. Click OK.

Once the schedule is configured, click Next on the Select Plan Properties dialog.

On the Select Maintenance Tasks dialog, select the Check Database Integrity checkbox and click Next.

If multiple maintenance tasks have been configured, you can order them in the Select Maintenance Task Order dialog. Only one task is configured, then no action is needed here. Click Next.

In the next dialog, select the name of the database where the maintenance task will be executed. Select the FileStream_Demo database from the Databases drop-down box and click Next.

In the next dialog, select the logging option. You can write the status of execution of the maintenance plan to a text file, or you can send an alert email. Select the desired option and click Next.

In the next dialog, you can view the summary of tasks performed to create the maintenance plan. Review them and click Finish.

Once the plan is created, the Maintenance Plan Wizard completes successfully.

Create a custom maintenance plan using the Maintenance Plan Designer surface

As I explained, the Maintenance Plan Wizard has some predefined basic tasks that do not give more control and customization options. For example, if you want to run an SQL script before executing any maintenance task, it cannot be performed using the wizard.

To create a custom maintenance plan, open SQL Server Management Studio, expand Management, right-click Maintenance Plans, and select New Maintenance Plan.

In the dialog, specify the desired name of the maintenance plan and click OK.

The Maintenance Plan Designer opens. It has two sections. In the first section, you can specify the name and description of the maintenance plan. You can change the properties of the existing subplan or add the subplans to the existing maintenance plans.

The description of individual components:

When you create a new maintenance plan, SQL creates a default subplan named Subplan_1. We can edit the properties of it. To edit the default subplan, click the Edit subplan properties button. Alternatively, select the subplan from the grid view and double-click it. The Subplan Properties dialog box opens. Specify the desired name of the subplan and click OK. 

As I mentioned earlier, we will create a maintenance job to perform the database consistency check. Drag Check Database Integrity Task from the Maintenance Plan toolbox and drop to the Maintenance Plan Designer surface.

Double-click Check Database Integrity Task. The Check Database integrity task dialog box opens.

In the dialog box, select the SQL Server connection from the Connection drop-down box. You can also create a new connection by clicking the New button. When you click the Database(s) drop-down box, a dialog box with multiple options opens. You can perform consistency checks on:

1. All databases
2. Only system databases (master, msdb, model, TempDB)
3. User databases
4. Specific databases.

You can also ignore the list of databases which are offline. As mentioned, we want to perform a consistency check on the FileStream_Demo database. Select FileStream_Demo from the These Databases list and click OK.

Once the check database integrity task is complete, schedule its execution time. To do that, click the Calendar button in the toolbar.

Alternatively, you can double-click the subplan to open properties and click the Calendar button beside the Schedule text box.

The New Job Schedule dialog box opens. As mentioned, the job must execute every Sunday at 12:00 AM, hence create the schedule accordingly. In the Name text box, specify the desired name of the job, select the desired schedule type. In the Frequency section, select Weekly in the Occurs text box. The job will execute once a week, hence select 1 in the Recurs every text box. Beneath, select Sunday. In the Daily Frequency section, specify the time in the Occurs once at text box. The job will be executed at 12:00 AM, hence enter 12:00:00 AM in it. And finally, select the start date of the job and click OK.

Once the schedule is created, you can see the summary of the schedule in the Schedule column of the subplan grid view.

Once the maintenance plan is created, save it. You can view the maintenance plan in the Maintenance Plan directory under Management.

The SQL Server Agent Service executes the maintenance plan; hence the schedule of the maintenance plan is listed under the Job directory. To view it, Open SSMS, expand SQL Server Agent, and expand Jobs.

You can execute the maintenance plan manually. To do that, right-click the Maintenance Plan and click Execute.

The maintenance plan completes successfully. You can view the report by clicking the Report button and selecting the View Report option.

You can also move the existing maintenance task so another server by exporting it to the file system and deploy manually on another server. As I mentioned, database maintenance plans are integration service packages stored in the MSDB database. To export maintenance plans to the file system, connect to integration services using SQL Server Management Studio, expand the Stored Packages node, then expand Maintenance Plans, right-click the maintenance plan named Database Consistency Check Job and select Export Package.

The Export Package dialog box opens. In the Package Location drop-down box, select File System. In the Package Path text box, enter the location where the maintenance plan should be saved to and click OK.

The maintenance plan will be exported to the desired location.

Summary

In this article, I have explained:

1. What maintenance plans are;
2. Pre-requisites to create maintenance plans;
3. How to create customized maintenance plans;
4. How to create a maintenance plan using the wizard;
5. How to export a maintenance plan to the file system.

Useful tool:

dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.