Chapter 8: SQL Server 2005 Maintenance Practices


1 2 3 Page 2
Page 2 of 3

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.

1 2 3 Page 2
Page 2 of 3