Creating a SQL Server Maintenance Plan

SQL Server is a very capable database in terms of storage capacity and performance.

It can be used successfully to manage large stores of data and provide a database management system for anyone who needs it and can work with it properly.

In order to properly manage the database and keep the database performing at an optimal level, routine tasks must be taken care of on the database.

These routine tasks include SQL Server maintenance jobs that will not only keep the data and the engine performing at satisfactory levels, but will also keep the data backed up in order to aid in the occurrence of a disaster.

The only way to be ready for a disaster is to plan ahead and ensure that the SQL Server maintenance plan a nd disaster recovery measures you have in place will work.

A good maintenance plan can do wonders if planned correctly, implemented successfully and managed appropriately and properly.

SQL Server provides a maintenance plan that help to implement appropriate measures to ensure that your database is safe and properly maintained.

So what is a database maintenance plan and how can you configure a good SQL Server maintenance plan?

A maintenance plan is a set of measures taken to ensure that a database is properly maintained and that routine backups are scheduled and handled.

Within SQL Server, a database maintenance plan can include tasks such as backups of the database, backups of the transaction log, recomputing statistics on the database, managing indexes and taking care of internal data storage issues.

A nice feature of SQL Server is that the maintenance plan can be configured by a wizard, which can help alleviate some of the burden of creating the plan. It can also ensure that the common features of the maintenance plan are taken care of.

The Database Maintenance Plan Wizard relies on a graphical user interface to give the user the ability to visually create and implement one or more jobs within SQL Server that will run under SQL Server Agent.

These jobs can automatically check the database integrity, get rid of unused space within the database, which will increase performance, reorganize your indexes, create the backups of the database and transaction logs.

For most administrators and instances, the wizard will suffice in creating the backup plan unless you want to create a custom maintenance plan yourself.

In order to use the wizard to create the maintenance plan in Enterprise Manager, select the Tools menu option and navigate to the Database Maintenance Planner option. This will bring up the following screen:

Click the Next button to continue with the wizard. This will bring up the following screen:

This screen allows you to specify the database(s) that will be involved with the maintenance plan that you are creating. The available options are

• All databases
• All system databases (master, model, and msdb)
• All user databases (all other than master, model, and msdb)
• These databases - which allows a selection from a list of one or more databases to include

Depending on which databases you would like to include in the SQL Server maintenance plan, make the selection from this screen and continue with the wizard.

Clicking the Next button will allow you to continue with the creation of the database maintenance plan. The Next button will open the following screen:

The first option in this dialog will instruct the plan to reorganize the data and index pages in the database.

If this option is selected, the statistics are recreated automatically when this step runs.

Updating the statistics will increase the performance and reliability of the database(s) in question. If you select the option to reorganize then you cannot select the update statistics box because the statistics will automatically get recreated.

You can however, just select the update statistics box without the reorganization. You can also specify that you would like to remove unused space from the database files.

During the course of normal operations, database files will become filled with unused space, which will lead to decreased performance.

After these options are selected, click the Next button to continue with the wizard and the following screen is shown:

This screen allows you to check the integrity of the database.

The integrity check can be configured to include indexes and attempt to repair any problems that it finds.

The checks can also be setup to be performed before doing a backup. This option can have negative side effects either way.

The integrity check may take up precious time on the server, but it can fix problems. On the other hand, the check may not be able to fix all the problems that it encounters. The check can also be scheduled by clicking the Change button to set up the schedule.

Selecting the Next button will bring up the following screen:

This screen allows the creation and schedule of the backup and to specify the location of the backup file, as well as, to verify the integrity of the backup when the operation is complete.

If you select the disk option on this screen you will be taken to the screen to configure the file location and select options to remove older backup files that may be in that location already.

This screen is shown in the following illustration:

Clicking Next will allow you to specify information relating to the transaction log backup as shown in the following image:

This screen functions the same way as the screen for configuring the database backup. It allows the selection of the file location, the verification of the integrity and the scheduling of the backup.

After clicking Next, if disk was selected as the location of the backup file, the user will be presented with the following screen:

This screen allows the selection of the directory location for the backup file, as well as the ability to instruct SQL Server to create a subdirectory for each database being backed up while the operation is running.

You can also specify the removal of old backup files and assign an extension to the backup files. Clicking on the Next button will bring up the following screen:

This allows you to specify whether or not to generate a report for the SQL Server maintenance plan and store that in a specific location.

You may also delete report files older than a specified timeframe. Operator email notifications can also be configured on this page as well.

Selecting Next will bring up the following screen for input:

This screen deals with options relating to whether or not the SQL Server maintenance plan history will be stored in a SQL Server database somewhere for easy retrieval and historical purposes.

Selecting Next will finalize the maintenance plan and bring up the confirmation screen as seen in the following illustration:

The objects generated by these steps can be seen by looking at the SQL Server maintenance plan for the database or looking at the individual jobs created and scheduled on the server.

Return to SQL Server DBA