SQL Server transaction log backups always fail with maintenance plan

By:   |   Comments (10)   |   Related: > Maintenance

One nice feature of SQL Server is the ability to create maintenance plans for all different types of maintenance activities such as backups, index rebuilds, integrity checks etc...  One part of the backup process is the ability to create transaction log backups.  One thing you may have noticed is that when you set this up and the job runs it always fails.  You check the backup files and the backup files are created, so why does this job always say it failed?

When setting up SQL Server maintenance plans you have the ability to pick and choose which databases you want to perform the maintenance task on as well as run both complete and transaction log backups.  In most cases for backups, you usually want to backup all of your databases, so the choice of all databases is often selected, but this sometimes creates a problem for transaction log backups.

Another thing you should always do when creating maintenance tasks is to check the  "Write report (to) a text file in directory", so you can get more details as to what is occurring during the maintenance task, especially if there are failures.



After you have everything setup and your scheduled jobs run, the full backups run without a problem, but the transaction log task errors as follows:


When looking at the job history you are only presented with the information below. This doesn't tell you much more except there was an issue with sqlmaint.exe.


To take this a step further you should look at the log file that was created when this maintenance task ran, but there is nothing that says anything about failures except for the last line that just says that the SQLMAINT.EXE process exit code = 1 (failed) which is the same thing as the job error above.  The only other information in this log file is the fact that some of the databases were ignored, but it doesn't mention anything about failures.

Sample Log File

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'TESTSERVER' as 'TESTSERVER\DBA' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 4/4/2007 1:14:14 PM

[1] Database AdventureWorks2000: Transaction Log Backup...
Destination: [C:\Backup\AdventureWorks2000_tlog_200704091322.TRN]

** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database AdventureWorks2000: Verifying Backup...

** Execution Time: 0 hrs, 0 mins, 1 secs **

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

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

[3] Database Northwind: Transaction Log Backup...
Destination: [C:\Backup\Northwind_tlog_200704091322.TRN]

** Execution Time: 0 hrs, 0 mins, 1 secs **

[4] Database Northwind: Verifying Backup...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[5] Database ReportServer: Transaction Log Backup...
Destination: [C:\Backup\ReportServer_tlog_200704091322.TRN]

** Execution Time: 0 hrs, 0 mins, 1 secs **

[6] Database ReportServer: Verifying Backup...

** Execution Time: 0 hrs, 0 mins, 1 secs **

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

End of maintenance plan 'DB Maintenance Plan1' on 4/9/2007 1:22:26 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)


So what is the issue
To look further if we run the following query we can take a look at the recovery model for each of our databases.

FROM master.dbo.sysdatabases

As we can see from the output below, the master, msdb, ReportServerTempDB databases are all set to the SIMPLE recovery model.  One of the things that you can not do in SQL Server is run a transaction log backup on a database in the SIMPLE recovery model.  This is why the above steps were ignored in our log output. This still doesn't explain why the job failed.


Just for the heck of it, let's change our maintenance plan task for transaction log backups to not include these databases that are in the SIMPLE recovery mode.

If we run the job again, the job is successful. 


And if we look at our job history we can see the job and the job step succeeded.


So from this simple lesson you can see that even though you have the ability to select all databases and the process still completes, the SQL Agent job reports it as a failure.  To get around this issue in SQL Server 2000 you need to either select the databases that are not in the SIMPLE recovery model or change your databases to the FULL or Bulk-Logged recovery models for this to work.

On thing to note, this issue has been fixed in SQL Server 2005.  The databases in the SIMPLE recovery model are ignored and the job is marked as successful instead of as failed.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, October 6, 2014 - 10:12:00 AM - Greg Robidoux Back To Top (34845)

Hi Sean, in the Maintenance Plan tool there is an option called Reporting and Logging.  This icon looks like two pieces of paper.  On my screen it is to the left of the Manage Connections... option.


Monday, October 6, 2014 - 9:52:16 AM - Sean Perkins Back To Top (34844)


I've got 2005, 2008 R2, and 2012 in my environment.

Friday, October 3, 2014 - 5:19:46 PM - Greg Robidoux Back To Top (34833)

Hi Sean, what version of SQL Server are you using?  This tip was written for SQL Server 2000 maintenance plans.


Friday, October 3, 2014 - 4:49:50 PM - Sean Perkins Back To Top (34832)


How to I get to option screen shown in the first screenshot of this tip?

Tuesday, April 8, 2014 - 7:00:14 AM - Paramjit Kaur Back To Top (30003)


Thursday, February 6, 2014 - 3:10:34 PM - kavitha Back To Top (29359)

Nice explanation but I also faced the same issue in my environment, but the job is running successfully after some runs all by itself, as per the above expalantion if the problem exists on other databases simple recovery model then every run of the job should be a fail...please help me in better understanding of the same.

Thursday, July 25, 2013 - 4:44:37 AM - Tony Back To Top (25993)

Great instructions - just what I needed.  Thanks

Wednesday, May 9, 2012 - 6:35:11 AM - kaviha Back To Top (17351)



Transactionlog backup job fail what are the reasons

Sunday, May 6, 2012 - 7:33:44 AM - Rene Back To Top (17287)

Excellent article, this will help a lot.

Could not find a clear explanation on the Web until this, Thanks !


Wednesday, August 13, 2008 - 7:27:15 AM - Daneille Back To Top (1629)

Excellent Tip, this was exactly what I was looking for, thank you.

get free sql tips
agree to terms