Share via

SQL Server Maintenance Plans

  • Article

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is backed up on a regular basis, and is free of inconsistencies. Maintenance plans create an Integration Services package that is run by a SQL Server Agent job. These maintenance tasks can be run manually or automatically at scheduled intervals. You can use the Maintenance Plan Wizard to create core maintenance plans, but creating plans manually gives you more flexibility.

Maintenance plans provide the following features:

  • Create or edit a task work flow. Tasks in each plan can be grouped into subplans that can be scheduled to run at different times.
  • Support for multi-server plans that can be used in master/target server environments.
  • Support for logging maintenance plan history to remote servers.
  • Support for Windows Authentication and SQL Server Authentication. When possible, use Windows Authentication.

Many DBA’s prefer Transact –SQL scripts for database maintenance tasks because scripting can be made more granular and scripts can be customized compared to packaged maintenance plans, especially compared to plans created with the Maintenance Plan Wizard.

Whether you script your solution, create a maintenance plan using the design surface in Management Studio, or create a maintenance plan using the Maintenance Plan Wizard in SQL Server Management Studio, it is important to implement a thoughtful and complete solution, and test it.

The results generated by maintenance tasks in a maintenance plan can be written as a report to a text file, or written to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in msdb. To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

Considerations for maintenance plans include:

  • Backup databases and transaction logs
  • Store backups both securely and off-site
  • Index fragmentation
  • Statistics
  • Regular consistency checks
  • Adequately sizing data files and transaction log files
  • Run additional SQL Server Agent jobs
  • Testing your backup/restore strategy on another computer or in a different environment

Note that maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower.

Important security considerations:   You must be a member of the sysadmin role to create and manage maintenance plans, and to view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or SQLAgentOperatorRole fixed database roles. Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

Back up user databases

User databases must be backed up on a regular basis. Also, do an additional round of backups after a new database or index is created, and after non-logged operations are performed.

There are four different backup strategies you can incorporate into your maintenance plans, each with its own strengths and weaknesses. As a DBA, you must weigh each aspect of the database application to reach a decision about the best solution for the system, the users, and the application administrators. Database size and frequency of data modification help determine the time and resources required to implement a database backup strategy.

The four types of backups supported for SQL Server are:

  • Back up only the database
  • Back up the database and the transaction logs
  • Differential database backups
  • Back up files or filegroups

Back up only the database

With this strategy, the entire database is backed up regularly. In case of failure, all the committed transactions that occurred after the most recent database backup are lost. The primary advantage of using this strategy is simplicity. The backup is obtained with a single operation, usually scheduled at regular intervals. When a database restore is required, it can be performed in one step.

Use full database backups when:

  • The database is small so that the backup can be completed in a reasonable amount of time.
  • The database has few data updates or modifications between backups.
  • You are willing to accept the loss of changed data if the database fails between backups and must be restored to its previous state.

Back up the database and the transaction logs

With this strategy, the entire database is backed up less frequently because the transaction log is backed up frequently between database backups. In case of failure, you will be able to recover all backed-up transactions and possibly committed – complete - transactions that have occurred since the last transaction log backup. In this scenario, only uncommitted – incomplete - transactions will be lost.

Use database and transaction log backups when:

  • The database is large or predicted to grow large in the near future.
  • There are substantial updates or data modifications taking place on the database between backups.
  • If you have a requirement to recover the database to as recent a state as possible – for example, to not lose any transactions already committed.
  • You cannot afford to lose changes since the most recent database backup.

Differential database backups

This strategy is used in addition to either the database backup strategy or a database and transaction log backup strategy. Differential backups contain only database changes since the last database backup. The first step in this strategy is to perform a complete database backup. Then you can schedule the transaction log backups, followed by a differential backup. The differential backup strategy combined with the transaction log backup strategy reduces the number of transaction log backups that need to be restored during a recovery operation.

Use differential backups when:

  • If you have a requirement to recover the database to as recent a state as possible – for example, to not lose any transactions already committed.
  • You cannot afford to lose changes since the most recent database backup.
  • The amount of time spent in recovering the database from transaction logs is not acceptable.

File or filegroup backups

File or filegroup backups are a specialized method of database backup where only certain files or filegroups from a database are backed up. This is usually done when there is not enough time to perform a full database backup. To make use of file and filegroup backups, you must utilize transaction log backups, as well.

Use file or filegroup backups when :

  • For example, the databases to be backed up are very large databases partitioned among four files. If you are limited to a 60-minute maintenance window each night, but a database backup would take three hours, take a backup of one file each night, and ensure data consistency by taking transaction log backups at short intervals during the day.

Rebuild indexes

  • Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. For more information, see Fill Factor.
  • Compress data files by removing empty database pages.
  • Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.

Check database consistency

  • Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.

Run SQL Server Agent Jobs

  • SQL Server Agent jobs can be used to perform a variety of actions made up of job steps, and also to run the actions and jobs that make up a maintenance plan.

Store backups off-site

  • The primary objective of taking database backups and testing restore procedures is to enable data recovery in case of data loss or disaster, so you must store backup media away from the physical location of your production environment.

Additional resources

SQL Server 2008 R2 Books Online:  How to: Create a Maintenance Plan

Brad McGehee’s free online e-book:  Brad’s Sure Guide to SQL Server Maintenance Plans

Script an existing maintenance plan:  https://social.msdn.microsoft.com/forums/en-US/sqltools/thread/52d71331-908d-4d5e-a14a-c665ce4cbc6a/

About the Accidental DBA:  https://blogs.technet.com/b/accidental_dba/archive/2011/05/23/introducing-the-accidental-dba.aspx

Contact the Accidental DBA:  Accidental_DBA_MSFT@hotmail.com