Maintenance Plan Tasks

Check Database Integrity Purpose:

  • The DBCC CHECKDB performs an internal consistency check
  • very resource intensive
  • perform it on a regular basis

Shrink Database

  • Shrinking a database is a very poor practice in the SQL world.  Don’t do it

Rebuild Index

  • The Rebuild Index task physically rebuilding indexes from scratch
  • This removes index fragmentation and updates statistics at the same time

Reorganize Index

  • The Reorganize Index task helps to remove index fragmentation, but does not update index and column statistics
  • If you use this option to remove index fragmentation, then you will also need to run the Update Statistics task as part of the same Maintenance Plan

Update Statistics

  • The Update Statistics task runs the sp_updatestats system stored procedure against the tables
  • Don’t run it after running the Rebuild Index task, as the Rebuild Index task performs this same task automatically

Execute SQL Server Agent Job

  • The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs (ones you have previously created), and to execute them as part of a Maintenance Plan

History Cleanup

  • The History Cleanup task deletes historical data from the msdb database
  • backup and restore
  • SQL Server Agent and Maintenance Plans

Back Up Database (Full)

  • The Back Up Database (Full) task executes the BACKUP DATABASE statement and creates a full backup of the database

Back Up Database (Differential)

  • The Back Up Database (Differential) task executes the BACKUP DATABASE statement using the DIFFERENTIAL option

Backup Database (Transaction Log)

  • The Backup Database (Transaction Log) task executes the BACKUP LOG statement, and, in most cases, should be part of any Maintenance Plan that uses the Back Up Database (Full) task

Maintenance Cleanup Task

  • Use a batch file to clean up files