Microsoft SQL server management studio has built-in tools such as Maintenance Plan Wizard and Maintenance Plan designer to create a variety of typical tasks under a maintenance plan. The scheduled jobs are run by SQL server agent and perform the following tasks:

Backup database task

Backup database task is added to maintenance plan to perform full, differential and transaction log backups. It has many features:

• Provide the following options to backup databases: All databases, system databases, user databases or any specific databases.
• Type of backup: Full, Differential, Transaction log backup.
• Creating sub-directories for each database.
• Compressed backups to increase the backup speed.
• Scheduling the data and log backups.

Rebuild Index and Reorganize Task

Database indexes can be fragmented in two ways, internal and external fragmentation. Internal fragmentation is a free space on the page due to data insertion, deletion, and data update in a table. Whereas external fragmentation occurs when the order of database logical and physical pages is not contagious. Internal fragmentation can occur in fixed size memory allocation, external fragmentation occurs in dynamic memory allocation.

Rebuild Index task and reorganize tasks help to maintain the database indexes by dropping & re-creating the indexes and moving the index pages, respectively. These tasks can be performed either by complex SQL scripts or by MS SQL server maintenance plan wizard.

History cleanup task

The MSDB system databases records historical data generated by backup, restore and other SQL server agent jobs. Over the time, it is necessary to clean the outdated data. History cleanup task removes the old data from MSDB database.

Maintenance cleanup task

This task is used for removing obsolete full, differential and transaction log backup files and report files from the location where backup files are stored. This task avoids unnecessary use of storage space.

Check database integrity task

This task examines the allocation and structural integrity of user and system tables and indexes in database, and ensures that any integrity glitches with the database are identified & reported.

Shrink Database Task

In some cases, SQL server database uses additional space than its data consumes. Shrinking database task diminishes the size of the database and return the storage space to operating system. This task is not often recommended unless the minimum amount of disk space is available.

T-SQL Statement Task

Used to customize the maintenance plan by adding Transact-SQL statements to maintenance plan.

Execute SQL Server Agent Job Task

Used to execute Microsoft SQL Server Agent jobs within a maintenance plan.

Update Statistics Task

Defines the database(s) on which the table and index statistics will be updated.

Maintenance plans can be developed from SQL Server Management Studio (SSMS) as well as using T-SQL code. In older versions of SQL Server, maintenance plans have some limitations. If there is a need to create flexible database maintenance solutions, it is better to do so manually using T-SQL code, instead of using maintenance plans. In newer versions of SQL Server, maintenance plans have become more flexible and useful. There are two ways to create maintenance plans from SSMS – the Maintenance Plan Wizard and the Maintenance Plan Designer.

At Britec, our programming team offers SQL maintenance plans with a variety of maintenance tasks to boost up database performance.