BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan (303292)

SYMPTOMS

A maintenance plan job to backup transaction logs may fail. The "Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'" job history may show the following error message:

The job failed. The Job was invoked by Schedule 3 (Schedule 1). The last step to run was step 1 (Step 1).

The job step details history contains this error message:

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

If the Write history to the table msdb.dbo.sysdbmaintplan_history reporting option is selected the history of the plan contains an entry similar to:

Backup can not be performed on this database. This sub task is ignored

If the Write report to a text file in directory reporting option is selected the report file contains this entry:

Backup can not be performed on database 'pubs'. This sub task is ignored.

In addition, expired transaction log backups may not be deleted. Although the maintenance plan job fails, all backups are performed correctly.

This behavior occurs under the following circumstances:

Multiple databases are backed up in a single Maintenance Plan.
The Maintenance Plan is configured to perform transaction log backups.
The recovery model of at least one of the databases in the Maintenance Plan is set to SIMPLE.

CAUSE

Selecting the "Backup the transaction log as part of the maintenance plan" option directs SQLMaint.exe to execute a BACKUP LOG command on each database in the plan.

Issuing a BACKUP LOG command against a database in SIMPLE recovery mode causes this error message:

Server: Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

SQLMaint.exe interprets this as a non-fatal error and continues to back up the transaction logs of other databases in the Maintenance Plan. However, after the log backups are complete SQLMaint exits without removing expired backups.

WORKAROUND

To work around this problem either:

Create two Maintenance Plans. One Maintenance Plan contains only databases that use the FULL or BULK LOGGED recovery models. The second Maintenance Plan contains databases in SIMPLE recovery mode. Do not perform transaction log backups in the second Maintenance Plan.
Change the recovery model of the databases from SIMPLE to either FULL or BULK LOGGED and perform a full database backup on the databases.

MORE INFORMATION

The SIMPLE recovery model is equivalent to selecting the Select Into/Bulk copy and Trunc. log on Chkpt options in previous versions of SQL Server. You cannot perform a transaction log backup for databases that have their recovery model set to SIMPLE.

REFERENCES

For more information, refer to the "Selecting a Recovery Model" topic in SQL Server Books Online.