Skip Links

Microsoft Subnet: An independent Microsoft community

Chapter 8: SQL Server 2005 Maintenance Practices


By Ross Mistry, Chris Amaris, Alec Minty, and Rand Morimoto, Network World
November 01, 2007 09:55 AM ET

For SQL Server databases to perform at optimal levels, a database administrator (DBA) should conduct routine maintenance on each database. Some of these routine database tasks involve rebuilding indexes, checking database integrity, updating index statistics, and performing internal consistency checks and backups. These routine database maintenance tasks are often overlooked because they are redundant, tedious, and often time consuming. Moreover, today's DBAs are overwhelmed with many other tasks throughout the day. In recognition of these issues, SQL Server provides a way to automate or manually create these routine DBA chores with a maintenance plan. After the maintenance tasks are identified and created, routine maintenance should commence daily, weekly, monthly, or quarterly, depending on the task. Ultimately, these tasks will put organizations on the path to having healthier, consistent, and more trustworthy databases.


What's New for Maintenance with Service Pack 2

With SQL Server Service Pack 2, many new improved features and fixes have bolstered the maintenance plan creation experience. These changes include the following:

  • The Maintenance Plan designer supports multiple subplans within a maintenance plan and the functionality to create independent schedules for each subplan. Multiple Schedules is a highly anticipated feature that can be leveraged to set separate schedules for items such as backups, updating statistics, and executing SQL Server jobs.

  • Upon the launch of SQL Server 2005, the installation of SQL Server Integration Services (SSIS) was warranted if organizations wanted to run maintenance plans. This has since changed. Integration Services is no longer required because maintenance plans are now a fully supported feature within the Database Engine.

  • For increased administration, maintenance plans now support multiserver environments and logging maintenance plan information to remote servers. You can now configure maintenance plans for all target servers from one central master server.

  • The Maintenance Cleanup Task, which first appeared in SQL Server 2000, has reemerged in the maintenance plans to be enjoyed by every DBA. To review, this task removes any remaining files that were part of the maintenance plan execution.

Other improvements and enhancements are seen with the introduction of SQL Server 2005 Service Pack 2. The following reveals some of the fixes that more than a few people have anticipated to improve specific tasks.

  • With the release of SQL Server 2005 Service Pack 2, new backup expiration options have been added to the Database Backup maintenance plan task. You can specify backup expiration options, such as having the backup set expire after a specific date or on a specific date. This feature existed in SQL Server 2000; however, it was not available when SQL Server 2005 was released.

  • If you select a specific location to store the backup folder, the Database Backup maintenance plan task will not reset this option.

  • In the past, when using the Backup Database maintenance plan task, it was possible to create differential and transaction log backup plans on system databases that were using the Simple Recovery model. This bug has been addressed.

  • The History Cleanup maintenance plan task offers the option to delete files by selecting a unit of time in hours, ultimately reducing manual work.

  • The Update Statistics task includes options for full scan or for sample size, as was available in SQL Server 2000 maintenance plans.


Establishing a SQL Server Maintenance Plan

A maintenance plan performs a comprehensive set of SQL Server jobs that run at scheduled intervals. The maintenance plan conducts scheduled SQL Server maintenance tasks to ensure that relational databases within the database engine are performing optimally, conducting regular backups, and checking for anomalies. The Database Maintenance Plan, a feature included within the SQL Server Database Engine, can be used to automatically create and schedule these daily tasks. A comprehensive maintenance plan includes these primary administrative tasks:

  • Running database integrity checks

  • Updating database statistics

  • Reorganizing database indexes

  • Performing database backups

  • Cleaning up database historical operational data

  • Shrinking a database

  • Cleaning up leftover files from the maintenance plan

  • Executing SQL Server jobs

  • Cleaning up maintenance tasks

Note - Unlike SQL Server 2000, Log Shipping is no longer a task included with the maintenance plan. Log Shipping can be configured at the database level in SQL Server Management Studio (SSMS) or via Transact-SQL (TSQL) scripts.


Check Database Integrity Task

The Check Database Integrity Task verifies the health and structural integrity of both user and system tables within relational databases selected in the SQL Server Database Engine. When running this task, you have the option to also check the integrity of all index pages. This specific task can be created in the Maintenance Plan Wizard, which will manually create a Maintenance Task. On the other hand, you can use TSQL to create this task. When you create the Database Integrity task, the database options available include all system databases, all user databases, or specific databases.

Although the following is a basic syntax, it supplies the information you need to assess the health and integrity of the database on the AdventureWorks database.

USE [AdventureWorks]

Our Commenting Policies
Latest News
rssRss Feed
View more Latest News