Chapter 8: SQL Server 2005 Maintenance Practices


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]

Shrink Database Task

The Shrink Database Task reduces the physical database and log files to a specific size, similar to the Automatic Shrink Task available in SSMS. When creating a maintenance task, you can shrink all databases, all system databases, all user databases, or specific databases within a single task. This operation removes excess space in the database based on a percentage value you enter. In addition, thresholds in MB can be entered, indicating the amount of shrinkage that needs to take place after the database reaches a certain size and the amount of free space that must remain after the excess space is removed. Finally, free space can be retained in the database or released back to the operating system.

This TSQL syntax shrinks the AdventureWorks database, returns freed space to the operating system, and allows for 15% of free space to remain after the shrink:

USE [AdventureWorks]

Tip - When you create maintenance plans, it is a best practice not to select the option to shrink the database. First, when shrinking the database, SQL Server moves pages toward the beginning of the file, allowing the tail end of the files to be shrunk. This process can increase the transaction log size because all moves are logged. Second, if the database is heavily used and there are many inserts, the database files will have to grow again. SQL Server 2005 addresses slow autogrowth with instant file initialization; therefore, the growth process is not as slow as it was in the past. However, at times autogrow does not catch up with the space requirements, causing performance degradation. Third, constant shrinking and growing of the database leads to excessive fragmentation. If you need to shrink the database size, it should be done manually when the server is not being heavily utilized.

Reorganize Index Task

When there is a need to improve index scanning performance, look toward the Reorganize Index Task. This task defragments and compacts clustered and nonclustered indexes on all tables or views, or a particular table or view. The Reorganize Index Task can also be applied to all databases, system databases, user databases, or individually targeted databases. By also selecting an additional option, large object (LOB) data types such as images or text will also be included in the compacting process.

To gain better insight into the operation of this task, use the TSQL syntax that follows to reorganize indexes for the AdventureWorks [Sales]. [SalesOrderDetail] table. This example also includes the option to compact large objects:

USE [AdventureWorks]
ALTER INDEX [AK_SalesOrderDetail_rowguid] 
ON [Sales].[SalesOrderDetail] 
USE [AdventureWorks]
ALTER INDEX [IX_SalesOrderDetail_ProductID] 
ON [Sales].[SalesOrderDetail] 
USE [AdventureWorks]
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetail] 


Rebuild Index Task

The Rebuild Index Task aims at eliminating fragmentation by reorganizing all the table indexes in the database. This task is particularly good for ensuring that query performance and application response do not degrade. Therefore, when SQL is called on to conduct index scans and seeks, it operates at its full potential. In addition, this task optimizes the distribution of data and free space on the index pages, which allows for growth to take place faster.

The two rebuild index free space options consist of the following:

  • Reorganize Pages with the Default Amount of Free Space—Drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created.

  • Change Free Space per Page Percentage To—Drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages, and the larger the index grows. Valid values are from 0 through 100.

The rebuild index task advanced options consist of the following:

  • Sort Results in tempdb—The Sort Results in tempdb is the first Advanced option available in the Rebuild Index Task. This option is comparable to the SORT_IN_TEMPDB option for the index. When this option is enabled, the intermediate results are stored in tempdb during the rebuild of an index.

  • Keep Index Online While Reindexing—The second advanced option allows users to access the underlying table, clustered index data, and the associated indexes during the index rebuild operation.

Armed with the knowledge of what the Rebuild Index Task can do, use the following information to gain some hands-on experience. Use the Rebuild Index syntax that follows to rebuild indexes for the AdventureWorks [Sales]. [SalesOrderDetail] table. The option to Reorganize Pages Using the Default Amount of Free Space has been selected. This example will also sort results in tempdb and keep the index online while reindexing.

USE [AdventureWorks]
ALTER INDEX [AK_SalesOrderDetail_rowguid] 
 ON [Sales].[SalesOrderDetail] 
USE [AdventureWorks]
ALTER INDEX [IX_SalesOrderDetail_ProductID] 
 ON [Sales].[SalesOrderDetail] 
USE [AdventureWorks]
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] 
 ON [Sales].[SalesOrderDetail] 

Update Statistics Task

The Update Statistics Task ensures the data in the tables and indexes on one or more SQL Server databases are up to date by resampling the distribution statistics of each index on user tables.

There are numerous choices available to customize this task. Each of the options is explained next:

  • Databases—First select the databases that will be impacted by this task. The choices range from All Databases, System Databases, or User Databases and These Databases.

  • Object—After the databases are selected, decide in the Objects box whether to display both tables and views or only one of these options.

  • Selection—Choose the tables or indexes that will be impacted. If the Tables and Views option was selected in the Objects box, this box will be unavailable.

  • Update—The Update box offers three choices. Select All existing statistics if you need to update both columns and indexes. Select Column statistics if you need to update only column statistics, and select Index Statistics if you need to update only index statistics.

  • Scan Type—The Scan Type section allows you to update statistics based on a Full Scan or by entering a Sample By value. The Sample By values can be either a percentage or a specific number of rows.

The syntax to update statistics on the AdventureWorks [Sales]. [SalesOrderDetail] table with the advanced options to update all existing statistics and conduct a full scan is as follows:

use [AdventureWorks]
UPDATE STATISTICS [Sales].[SalesOrderDetail] 

History Cleanup Task

The History Cleanup Task offers organizations the perfect opportunity to remove historical data in a few simple steps. You can delete several types of data using this task. The following explains the options associated with this task.

  • Historical Data to Be Deleted—Use the Maintenance Plan Wizard to purge several types of data, including Backup and Restore history, SQL Server Agent Job history, and Maintenance Plan history.

  • Remove Historical Data Older Than—Use the wizard also to select the age of the data you want to delete. For example, you can choose to periodically remove older data based on daily, weekly, monthly, and yearly increments.

When the History Cleanup Task is complete, you can save a report to a text file or email the report to an operator by clicking Next. The Select Report Options screen is invoked and you must enable the check box Write a Report to a Text File, and then the storage location of the report by specifying the file and folder location.

The following TSQL example removes historical data older than four weeks for the following items: Backup and Restore history, SQL Server Agent Job history, and Maintenance Plan history.

declare @dt datetime select @dt = 
 cast(N'2006-12-28T09:26:24' as datetime) 
 exec msdb.dbo.sp_delete_backuphistory @dt
EXEC msdb.dbo.sp_purge_jobhistory 
EXECUTE msdb..sp_maintplan_delete_log null,

Execute SQL Server Agent Job

The Execute SQL Server Agent Job task allows you to run SQL Server Agent jobs that already exist as well as SSIS packages as part of the maintenance plan. This is done by selecting the job in the Available SQL Server Agent Jobs section in the Define Execute SQL Server Agent Job Task screen. Alternatively, TSQL syntax can be used to execute a job by entering the appropriate Job ID of a specific job that already exists.

The syntax to execute a SQL Server Agent Job is as follows:

EXEC msdb.dbo.sp_start_job @job_

Back Up Database Task

The Back Up Database Task is an excellent way to automate and schedule full, differential, or transaction log backups.

You can choose from an expanded set of options when creating full, differential, or transaction log backups with maintenance plans. With these expanded options, you can choose to back up a database or an individual component, set expiration dates, verify integrity, and even determine whether to use disk or tape. Each of the backup options is described in more detail next:

  • Specify the Database—A maintenance plan can be generated to perform a variety of backups, including backing up a single database, all databases, system databases, or all user databases.

  • Backup Component—The Backup Component section offers the option of either backing up the entire database or individual files or filegroups.

  • Backup Set Will Expire—To stipulate when a backup set will expire and can be overwritten by another backup, you need only to specify the number of days or enter a hard date such as September 5th, 1974, for the set to expire.

  • Backup Up To—This option allows the backup to be written to a file or a tape. A tape drive must be present on the system to back up to tape. The other option is having a backup written to a file residing on a network share.

  • Back Up Databases Across One or More Files—When selecting the backup destination, you can either add or remove one or more disk or tape locations. In addition, you can view the contents of a file and append to the backup file if it already exists.

  • Create a Backup File for Every Database—Instead of selecting the preceding option, Back Up Databases Across One or More Files, you can let SQL Server automatically create a backup file for every database selected. In addition, you can automatically create a subdirectory for each database selected.

  • Note - If the Automatically Create a Subdirectory option is selected, the new subdirectory created will inherit permissions from the parent directory. NTFS permissions should be used to secure the root folder to restrict unauthorized access.

  • Verify Backup Integrity—This option verifies the integrity of the backup when it is completed by firing a TSQL command that determines whether the backup was successful and is accessible.

Note - For a more thorough and detailed discussion of full, differential, and transaction log backups, see Chapter 17, "Backing up and Restoring the SQL Server 2005 Environment" (online).

You can choose to back up a database in one of three ways when you create a maintenance plan. Select the Define Back Up Database (Full) Task when it is necessary to capture the full database. Similarly, select Define Back Up Database (Differential) Task if it is important to record only data that has changed since the last full backup, or select the Define Back Up Database (Transaction Log) Task, which will back up only entries that are recorded to logs. The backup file extension for the Full and Differential Task is *.bak, whereas the Transaction Log Task is *.trn. Other than these noted differences, the options for each task are the same.

Caution - It is probably abundantly clear by now that maintenance plans are regularly used by DBAs to back up databases, including the transaction logs. A problem may occur during the restore process if you create a transaction log backup with the maintenance plan on a database that has already been configured for log shipping. Ultimately, two sets of transaction log backups are created, one from the maintenance task and the other from the log shipping task. Therefore, if a restore is needed, a combination of the transaction log backups is required to conduct the restore; otherwise, it is not possible to restore the database to the point of failure. If transaction log backups already exist based on log shipping, it is a best practice not to create additional transaction log backups with the maintenance plan. This will eliminate confusion and the potential of a botched restore resulting in lost data.

Maintenance Cleanup Task

The Maintenance Cleanup Task is used to delete files such as backups and reports that reside on the database after the maintenance plan is completed. There are many options for deleting data using this task:

  • Delete Files of the Following Type—You can choose to delete database backup files or maintenance plan text reports.

  • File Location—You can also choose to delete a specific file using the File Name box.

  • Search Folder and Delete Files Based on an Extension—You can delete numerous files with the same extension within a specified folder using this option; for example, all files with the extension *.txt. You can also select to delete all first-level subfolders within the folder identified with this option.

  • File Age—Files can be deleted by age. You will need to indicate the age of the files to be deleted. For example, you may choose to delete files older than two years. The unit of time also includes hours, days, weeks, and months.

Creating a Maintenance Plan

You can use several methods for creating a maintenance plan. You can use the Database Maintenance Plan Wizard from SQL Server Management Studio (SSMS), or you can manually create a maintenance plan using the tasks associated with the Maintenance Plan Tasks Toolbox. Review the next sections to appreciate how easy and straightforward it is to create a maintenance plan manually and with the wizard.

Creating a Maintenance Plan with the Wizard

Maintaining SQL Server databases is a vital activity for DBAs everywhere. A well-maintained system requires the use of a maintenance plan that automates administrative tasks according to each organization's needs. In this section the Maintenance Plan Wizard is used to create a customized maintenance plan of all system and user databases.

For this example, the steps include the following maintenance tasks: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, and Clean Up History. In a production environment, you should not include both the Reorganize Index and Rebuild Index task in the same plan. These tasks would be considered redundant because one task rebuilds the indexes from scratch and the other reorganizes the indexes. They have only been included for explanation purposes.

Note - A discussion of how to create databases and transaction log backups with the Maintenance Plan Wizard is discussed in Chapter 17 in the section titled "Automating Backups with a Maintenance Plan" (online).

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.

  2. In Object Explorer, first connect to the Database Engine, expand the desired server, expand the Management folder, and then the Maintenance Plans folder.

  3. Right-click Maintenance Plans and choose Maintenance Plan Wizard.

  4. In the Welcome to the Database Maintenance Plan Wizard screen, read the message and then click Next.

  5. In the Select Plan Properties screen, enter a name and description for the maintenance plan.

  6. Choose either the first option (Separate Schedules for Each Task) or the second option (Single Schedule for the Entire Plan or No Schedule). For this example, a separate schedule will be created for the backup plan. Click Next as shown in Figure 8.1.

  7. Figure 8.1

    Figure 8.1

    Scheduling and selecting the Maintenance Plan properties.

    New Feature with Service Pack 2 - The capability to create separate independent schedules for each subtask within a single maintenance plan is a new feature supported only with Service Pack 2.

  8. On the Select Maintenance Tasks screen, as shown in Figure 8.2, place a check on the following maintenance tasks: Check Database Integrity, Reorganize Index, Rebuild Index, Update Statistics, and Clean Up History, and then click Next.

  9. On the Select Maintenance Task Order page, select the order that the tasks should be executed and then click Next.

  10. Tip - Many maintenance tasks, including reindexing or updating statistics, alter the database when they run. In recognition of this situation, it is a best practice to make the full database backup maintenance task the first order of operation when prioritizing maintenance tasks. This ensures that the database can be rolled back if the maintenance plan tasks that change the database fail.

    Figure 8.2

    Figure 8.2

    Selecting database maintenance tasks.

  11. The first option in the maintenance plan is checking the database integrity. In the Define Database Check Integrity Task page, select All Databases from the drop-down list. The next item is to accept the defaults. Do this by validating that the Include Indexes Check is enabled, which will ensure all index pages and table databases have an integrity check run against them. Proceed to change the schedule by clicking Change and then set this task so it reoccurs every week starting during nonpeak times, such as Sunday at midnight. Click Next to proceed as in Figure 8.3.

  12. The second option selected is to Reorganize Index. From the drop-down on the Define Reorganize Index Task page, select All Databases. Ensure the option for Compact Large Objects is enabled. Set this task to occur once a week. Click Next to proceed as in Figure 8.4

  13. The Rebuild Index is the third task selected in the maintenance plan. On the Define Rebuild Index Task page, first select All Databases and then proceed to schedule this task to occur once a week. Select the Free Space Options to Reorganize Pages with the Default Amount of Free Space. In the Advanced Options section, enable Sort Results in tempdb and Keep Index Online While Reindexing, as shown in Figure 8.5. Click Next to proceed.

  14. Figure 8.3

    Figure 8.3

    The Define Database Check Integrity Task screen.

    Figure 8.4

    Figure 8.4

    The Define Reorganize Index Task screen.

    Figure 8.5

    Figure 8.5

    The Define Rebuild Index Task screen.

  15. For the fourth task, select All Databases from the database drop-down list in the Define Update Statistics Task page. Ensure that the default settings for All Existing Statistics update and Full Scan type are selected. Set this task to reoccur weekly. Click Next to proceed as in Figure 8.6.

  16. Figure 8.6

    Figure 8.6

    Specifying options on the Define Update Statistics Task screen.

  17. In the Define History Cleanup Task page, select the historical data to delete options, such as Backup and Restore History, SQL Server Agent Job History, and Maintenance Plan History. Select a value that will communicate when historical data will be deleted. This value should be based on the organization's retention requirements, as shown in Figure 8.7. Schedule the task to reoccur on a weekly basis and then click Next.

  18. Figure 8.7

    Figure 8.7

    Specifying options on the Define History Cleanup Task screen.

  19. In the Select Report Options page, set the option to either write a report to a text file and enter a folder location or email the report. To email the report, Database Mail must be enabled, configured, and an Agent Operation with a valid email address must already exist. Click Next to continue.

  20. The Complete the Wizard page summarizes the options selected in the Maintenance Plan Wizard. It is possible to drill down on a task to view advanced settings. Review the options selected, and click Finish to close the summary page.

  21. In the Maintenance Plan Wizard Progress screen, review the creation status as shown in Figure 8.8, and click Close to end the Maintenance Plan Wizard.

Figure 8.8

Figure 8.8

Viewing the Maintenance Plan Wizard Progress screen.

Manually Creating a Maintenance Plan

Maintenance plans can also be created manually with the aid of the Maintenance Plan (Design Tab). You can create a much more flexible maintenance plan with an enhanced workflow using the Maintenance Plan Design Tab compared to the Maintenance Plan Wizard, because it is equipped with better tools and superior functionality.

The experience of creating a maintenance plan manually has been further enhanced with the introduction of Service Pack 2. Maintenance plan history can now be logged to a remote server when you're creating a manual plan. This is a great new feature when managing many SQL Servers within an infrastructure because all data that is logged can be rolled up to a single server for centralized management.

Note - Creating manual maintenance plans with the Maintenance Plan (Design Tab) is very similar to the design surface available when creating packages with SSIS. For more information on creating Integration Service projects, see Chapter 5, "Administering SQL Server 2005 Integration Services."

The Maintenance Plan design surface, as shown in Figure 8.9, can be launched by right-clicking the Maintenance Plans folder and selecting New Maintenance Plan.

Figure 8.9

Figure 8.9

Viewing the Maintenance Plan design surface and toolbar screen.

You will find the Maintenance Tasks toolbox in the left pane of the Maintenance Plan (Design Page). You can drag maintenance tasks from this toolbox to the design surface in the center pane. If more than one task is dragged to the designer, it is possible to create a workflow process between the two objects by establishing relationships between the tasks. The workflow process can consist of precedence links. As such, the second task will only execute based on a constraint, which is defined in the first task such as "on success, failure or completion." For example, you can choose to create a workflow that will first back up the AdventureWorks database and then, on completion, rebuild all the AdventureWorks indexes, as illustrated in Figure 8.10.

The precedence constraint link between two objects can control the workflow if there is a statement to execute the second rebuild index task when the first backup task is successful. In this situation, when a backup task fails, the second task will not fire. As for creating a precedence constraint, you should first highlight both of the maintenance tasks in the designer, right-click, and then choose Add Precedence Constraint. Once the Precedence Constraint is created, either double click the connector arrow or right-click it and select Edit. This will bring up the Precedence Constraint Editor where you can define the constraint options, as shown in Figure 8.11.

Figure 8.10

Figure 8.10

Implementing a Precedence Constraint between two maintenance plan tasks.

Figure 8.11

Figure 8.11

Setting the Precedence Constraints on the Maintenance Plan Tasks screen.

In addition to creating precedence links, you also can execute tasks simultaneously. This is known as task parallelism and is commonly used when executing the same type of maintenance tasks on different SQL Servers. For example, you can execute a full backup of the master database on all the SQL servers from a central master SQL Server starting on Sunday at 9:00 p.m.

The final item worth mentioning is the reporting capabilities. After the maintenance plan is completed, you can create a report. To do this, locate the Reporting and Logging icon in the Maintenance Plan designer. The Reporting and Logging dialog box as shown in Figure 8.12 displays options such as Generate a Text File Report and Send Reports to an Email Recipient. Additional logging functionality exists, such as log extended information and log maintenance plan history to a remote server. The latter is a new Service Pack 2 feature.

Figure 8.12

Figure 8.12

Configuring Maintenance Plan Reporting and Logging options.

Tip - When working with maintenance plan tasks, you can use a View TSQL command button to convert the options selected for the task into TSQL syntax. This is a great feature for many DBAs who do not have an extensive background in programming.

Viewing Maintenance Plans

All maintenance plans can be viewed under the Maintenance Plan folder in SSMS and stored in SQL Server as jobs. They require the SQL Server Agent to be running to launch the job at the scheduled interval. If the SQL Server Agent is stopped, the jobs will not commence. In addition, all jobs can be edited or changed for ongoing support or maintenance.

Follow these steps to view the maintenance plan jobs in SQL Server Management Studio:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.

  2. In Object Explorer, first connect to the Database Engine, expand the desired server, expand SQL Server Agent, and then expand the jobs folder.

  3. Click Jobs to see a list of jobs created by the Maintenance Plan Wizard. The jobs are displayed in the Object Explorer Details tab located in the right pane; otherwise, the jobs are displayed under the Jobs folder in Object Explorer. This is shown in Figure 8.13.

Figure 8.13

Figure 8.13

Viewing Maintenance Plan scheduled jobs.

Note - One major difference with the scheduled job for the maintenance plan in SQL Server 2005 is that the scheduled job executes an SSIS package. The scheduled job in SQL Server 2000 used the SQLMAINT utility instead.

If the SQL Server Agent is not running, a dialog box appears, stating that the SQL Server Agent on the target server is not running. The SQL Server Agent must be started for SQL Server jobs to commence. Follow these steps to start the SQL Server Agent:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.

  2. In Object Explorer, first connect to the Database Engine, and then expand the desired server.

  3. Right-click SQL Server Agent and then click Start.

Creating Multiserver Maintenance Plans

In the past, DBAs encountered numerous challenges when managing more than one maintenance plan within their SQL infrastructure. The task of creating maintenance plans in a multiserver environment was exceedingly tedious because a maintenance plan had to be created on each and every server. Moreover, the task of verifying success, failure, and job history was equally difficult and time consuming; it had to be conducted on each server because a method to centrally manage these plans did not exist. To clearly illustrate just how difficult life could get for DBAs, it is worth mentioning that a typical global organization may have well over 100 SQL servers within its infrastructure; therefore, imagine the heartache and lack of operational efficiency that came along with managing maintenance plans.

Today, these nuisances have been alleviated. SQL Server 2005 Service Pack 2 offers support for multiserver maintenance plans. Specifically, you can now create maintenance plans for each of your SQL servers from a single central master server. This provides a significant difference in operational efficiency and administration.

To take full advantage of this new feature in Service Pack 2, a multiserver environment containing one master server and one or more target servers must be constructed before a multiserver maintenance plan can be created. It should be mentioned that target servers can be used only to view the maintenance plans. As a result, multiserver maintenance plans must be created and maintained on the master server so that you can provide regular maintenance of them.

Note - To create or manage multiserver maintenance plans, you must be a member of the sysadmin fixed server role on each of the SQL Servers.

Multiserver maintenance plans can be created with either the Maintenance Plan Wizard or by manually using the Maintenance Plan (Design Tab).

Establishing Maintenance Schedules for SQL Server

With each new release, SQL Server has become more self-maintaining. However, even with self-maintenance and automated maintenance plans, DBAs must conduct additional maintenance. Some maintenance procedures require daily attention, whereas others may require only yearly checkups. The maintenance processes and procedures that an organization follows depend strictly on the organization's individual environment.

The categories described in the following sections and their corresponding procedures are best practices for organizations of all sizes and with varying IT infrastructures. The following sections will help organizations establish sound maintenance practices to help them ensure the health of their SQL Server Database Engine. The suggested maintenance tasks that follow are based on daily, weekly, monthly, and quarterly schedules.

Daily Routine Maintenance Tasks

Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:

  • Check that all required SQL Server services are running.

  • Check Daily Backup logs for success, warnings, or failures.

  • Check the Windows Event logs for errors.

  • Check the SQL Server logs for security concerns such as invalid logins.

  • Conduct full or differential backups.

  • Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.

  • Verify that SQL Server jobs did not fail.

  • Check that adequate disk space exists for all database files and transaction logs.

  • At least monitor processor, memory, or disk counters for bottlenecks.

Weekly Routine Maintenance Tasks

Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine and are examined in the following list:

  • Conduct full or differential backups.

  • Review Maintenance Plan reports.

  • Check database integrity.

  • Shrink the database if needed.

  • Compact clustered and nonclustered tables and views by reorganizing indexes.

  • Reorganize data on the data and index pages by rebuilding indexes.

  • Update statistics on all user and system tables.

  • Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.

  • Manually grow database or transaction log files if needed.

  • Remove files left over from executing maintenance plans.

Monthly or Quarterly Maintenance Tasks

Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don't require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.

  • Conduct a restore of the backups in a test environment.

  • Archive historical data if needed.

  • Analyze collected performance statistics and compare them to baselines.

  • Review and update maintenance documentation.

  • Review and install SQL Server patches and service packs (if available).

  • Test failover if running a cluster, database mirroring, or log shipping.

  • Validate that the backup and restore process adheres to the Service Level Agreement defined.

  • Update SQL Server build guides.

  • Update SQL Server disaster recovery documentation.

  • Update maintenance plan checklists.

  • Change Administrator passwords.

  • Change SQL Server service account passwords.


The maintenance plan feature alone should be one of the key selling points for SQL Server 2005. The capability to use a wizard that is uncomplicated to automate administrative tasks that SQL Server will perform against a single database or multiple databases has decreased the amount of manual work DBAs must do and ensures that tasks do not get overlooked. You can also create plans manually. This is a good option for those looking for a lot of flexibility on advanced workflow.

SQL Server 2005 Service Pack 2 has also allowed organizations to extend their use of maintenance plans. The following are just some of the features Service Pack 2 has brought to the table. SQL Server 2005 Service Pack 2 offers support for multiserver maintenance plans, SQL Server 2005 no longer requires SSIS to be installed, and Service Pack 2 offers the potential for remote logging.

In the end, the most important thing to take away from this chapter is the importance of having a maintenance plan in place early and ensuring that maintenance is scheduled accordingly to preserve the health of each database.

Best Practices

Some important best practices from the chapter include the following:

  • DBAs should fully understand all maintenance activities required and implemented within the SQL Server environment.

  • Use the Maintenance Plan Wizard to automate and schedule routine maintenance operations.

  • When creating maintenance plans with the wizard, leverage the new features included in SQL Server Service Pack 2 and create independent schedules for subtasks.

  • Maintenance tasks should be scripted, automated, and fully documented.

  • Maintenance tasks should be conducted during nonpeak times or after hours, such as on weekends and after midnight.

  • When you configure the order of the maintenance tasks, backups should be executed first, and then other tasks that change the database.

  • Do not include the Shrink Task when creating Maintenance Plans. Manually shrink the database if needed during nonpeak hours.

  • Maintenance tasks should be grouped into daily, weekly, and monthly schedules.

  • Schedule and conduct routine maintenance tasks on a daily, weekly, and monthly basis.

  • For a large enterprise environment running many SQL Servers, take advantage of subplans and the multiserver maintenance plan.

Copyright © 2007 Pearson Education. All rights reserved.

Learn more about this topic

You Might Like
Join the discussion
Be the first to comment on this article. Our Commenting Policies