To work around this problem, DBAs often create two maintenance plans for each database, one to perform full backups with integrity checks and another to perform just log backups. While I was researching this article, I discovered another workaround. If you select the wizard option to perform integrity checks before backups, the scheduled jobs for full backups and log backups include the following two switches in the call to xp_sqlmaint:
-BkUpOnlyIfClean -CkDB
If you manually remove these two switches from the definition of the wizard-created transaction log backup job, the integrity checks won't execute before log backups, but they'll still execute before full backups. To find the transaction log backup job, expand the Management node in Enterprise Manager and go to Jobs under the SQL Server Agent node, as Figure 4 shows. If you haven't manually changed the job name since the wizard created it, the name is Transaction Log Backup Job for DB Maintenance Plan 'PlanName'.
You can use the same technique to fix the problem resulting from a bug that the Microsoft article "BUG: DB Maintenance Plan Cannot Be Modified to Include/Exclude Integrity Checks Before Backups" (http://support.microsoft.com/?kbid=264194) describes. This bug prevents you from modifying an existing plan to include or exclude integrity checks before backups, so you're stuck with the option you select when you create the plan. Given the limitation of the options screen and the behavior resulting from the bug, your best option is probably to maintain these two switches manually in the scheduled job commands.
Database Backup Options
After you configure the integrity checks, the wizard lets you set up the most important database-maintenance plan actions: complete backup and transaction log backup. The backup options, which Figure 5 shows, are identical for full and log backup actions. Unfortunately, maintenance plans don't support differential backups, even though they've been around since SQL Server 7.0. Maintenance plans don't support file backups or filegroup backups, either, so the full backup part of the maintenance plan isn't always suitable for large databases that would benefit from using file or filegroup backups. You can configure your plan to verify whether the backup set is complete and readable by selecting the Verify the integrity of the backup upon completion check box. If you have a tape drive, you can specify it as the backup location, but for performance reasons, experts generally recommend that you direct SQL Server backups to a local hard disk and back up the files from the hard disk to a tape. One of my favorite features of the sqlmaint utility is the option to automatically remove old files; you can set up retention periods ranging from minutes to months.
I ran into a few problems with transaction log backups in maintenance plans. If you include a log backup for a database that uses the Simple Recovery model, the wizard doesn't warn you that this operation is illegal, and the scheduled job correctly reports failure—but only in SQL Server 2000. SQL Server 7.0 has a bug that the Microsoft article "BUG: Sqlmaint Does Not Report Error on BACKUP LOG When Truncate Log on Checkpoint is Set" (http://support.microsoft.com/?kbid=242500) describes. It doesn't report this error as a failure. You might need to fix this problem when you upgrade to SQL Server 2000 if jobs that used to run with no errors start generating errors. This bug is somewhat related to a SQL Server 2000 bug documented in the Microsoft article "BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan" (http://support.microsoft.com/?kbid=303292). According to this article, if a maintenance plan performs log backups on multiple databases and at least one of the databases uses the Simple Recovery model, the sqlmaint utility doesn't remove expired backups for all databases. If you run into this problem, you must either configure all databases in the plan to use the Full or Bulk-Logged Recovery model or create a second maintenance plan for all databases that use the Simple Recovery model. You'd need to configure the plan to not perform transaction log backups.
Monitoring and Notifications
The Reporting tab, which Figure 6 shows, lets you define several reporting options for the maintenance plan. You can output status reports to text files and have SQL Server automatically delete them when they expire. The sqlmaint utility can alternatively output reports to HTML files, but the Reporting tab doesn't let you set up this option. Instead, you need to modify the calls to xp_sqlmaint in the scheduled jobs to include the -HtmlRpt and -DelHtmlRpt switches. You can also choose to log plan history to the sysdbmaintplan_history table either on the local or a remote server.
The easiest way to examine the history of maintenance-plan executions is to use the Database Maintenance Plan History dialog box, which Figure 7 shows. You open this dialog box by right-clicking the Database Maintenance Plans node in Enterprise Manager and selecting Maintenance Plans History. Because this form provides comprehensive filters, drilling down through the thousands of rows in the sysdbmaintplan_history table is easy. You can also write views to provide custom reports. If you manage multiple servers, you can set up all servers to write to the sysdbmaintplan_history table on just one server. Another option is to create a view that uses the UNION clause to read the sysdbmaintplan_history table on multiple linked servers. The history table and sqlmaint text reports provide more details about task failures than you can gather from the scheduled job history. When I notice in Enterprise Manager that a maintenance-plan job failed, I prefer to view the maintenance plan history because the scheduled job history often reports only "sqlmaint.exe failed" in the error description.
In mission-critical environments, you can't rely solely on someone regularly reviewing the history table or reading the plan reports to find out something went wrong. If a backup failure occurs, you want to be notified immediately so that you can take corrective action. Neither the wizard nor the maintenance-plan screen lets you set up notifications for maintenance-plan failures. To define notifications, you need to edit the scheduled jobs the wizard generated. SQL Server modifies the job schedule and the job command when you edit the maintenance plan, but it leaves the job's Notifications section intact. A scheduled job gives you three notification options: Email operator, Page operator, and Net send operator. If you later drop and recreate a maintenance plan, you need to set up these notifications again.
SQL Server database maintenance plans give you a powerful and flexible mechanism for defining and providing for your databases' maintenance needs. By creating optimal plans, you can vastly reduce the time you need to spend maintaining and monitoring your databases.
End of Article
Prev. page
1
2
[3]
next page -->
