Task 15: Maintenance Plan

To create a MaintenancePlan is pretty easy because there is a wizard there and the tasks are already defined.

Example of MaintenancePlan created on the project I work for: 

Maintenance plans can be created to perform the following tasks:

• 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 Specify Fill Factor for an Index.
• 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.
• 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.
• Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.
• Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions and the maintenance plans to run those jobs.