A transaction log backup job that is created in Database Maintenance Planner fails to execute (303229)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup
This article was previously published under Q303229

SYMPTOMS

The Database Maintenance Planner is used to schedule jobs to back up a database and its transaction log. The job to back up the database succeeds, but the job to back up the transaction log fails. When you view the job history, you see a message that resembles the following:
The job failed. The Job was invoked by Schedule 4 (Schedule 1). The last step to run was step 1 (Step 1).
To verify whether you are encountering this problem, you can examine the Database Maintenance Plan log file (if you chose to create one) which is stored in the Log folder for SQL Server (by default in C:\Program Files\Microsoft SQL Server\MSSQL\LOG for a default instance). If you see a message that resembles the following in the DB Maintenance Plan log file, the job failed because transaction log backups are disallowed on the database:
Backup can not be performed on database 'Northwind'. This sub task is ignored.

CAUSE

The transaction log backup job may have failed because the database is using the "Simple" recovery model in SQL Server 2000 or in SQL Server 2005.

WORKAROUND

If you are not relying on transaction log backups as part of your disaster recovery strategy, you could modify the maintenance plan to only perform full database backups.

If you are relying on transaction log backups, however, you will need to change the recovery model for that database to "Full" or "Bulk-Logged" in SQL Server 2000 or in SQL Server 2005.

In SQL Enterprise Manager, right-click the database and click Properties. Click the Options tab and select either Bulk-Logged or Full for the recovery model.

For a description of the three recovery models and their differences, see "Selecting a Recovery Model" in SQL Server Books Online.

MORE INFORMATION

In SQL Server 2000 or in SQL Server 2005, the "Simple" recovery model is equivalent to "truncate log on checkpoint" in earlier versions of SQL Server. If the transaction log is truncated every time a checkpoint is performed on the server, this prevents you from using the log for database recovery. You can only use full database backups to restore your data. Backups of the transaction log are disabled when the "Simple" recovery model is used.

In SQL Server 7.0, a job created by the Database Maintenance Plan Wizard to perform a transaction log backup would not fail even if the database was set to "truncate log on checkpoint". This was due to a bug in SQL Server 7.0; for additional information, see the following article in the Microsoft Knowledge Base:

242500 BUG: Sqlmaint Does Not Report Error on BACKUP LOG When Truncate Log on Checkpoint is Set

WARNING: Even though you change the recovery model of the msdb database to "Full", it will automatically be reset to "Simple" the next time that SQL Server Agent is restarted. This is by design to prevent the msdb transaction from growing to an unmanageable size. You will need to perform only full database backups on the msdb database.

The master database should also be left in the "Simple" recovery model; for more information, see the following article:

285288 INF: Transaction Log Backups of Master Database Are Not Allowed

Modification Type:MajorLast Reviewed:12/20/2005
Keywords:kbprb KB303229
©2004 Microsoft Corporation. All rights reserved.