Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. A transaction log backup job that is created in Database Maintenance Planner fails to execute

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

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.

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: kbappliestoyukon, kbyukonsweep, kbprb, kbbillprodsweep, kb

↑ Back to the top

Article Info
Article ID : 303229
Revision : 3
Created on : 4/20/2018
Published on : 4/20/2018
Exists online : False
Views : 150