Chapter 8: SQL Server 2005 Maintenance Practices


1 2 3 Page 3
Page 3 of 3

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

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2007 IDG Communications, Inc.

1 2 3 Page 3
Page 3 of 3