Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server transaction log backups always fail with maintenance plan


By:   |   Updated: 2007-04-10   |   Comments (10)   |   Related: More > Maintenance

Problem
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?

Solution
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.

SELECT name, DATABASEPROPERTYEX(name, 'Recovery')
FROM master.dbo.sysdatabases
ORDER BY 1

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



Last Updated: 2007-04-10


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, October 06, 2014 - 10:12:00 AM - Greg Robidoux Back To Top

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.

-Greg


Monday, October 06, 2014 - 9:52:16 AM - Sean Perkins Back To Top

 

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


Friday, October 03, 2014 - 5:19:46 PM - Greg Robidoux Back To Top

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

-Greg


Friday, October 03, 2014 - 4:49:50 PM - Sean Perkins Back To Top

 

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


Tuesday, April 08, 2014 - 7:00:14 AM - Paramjit Kaur Back To Top

THANKS FOR THE SOLUTION IT WOULD GREATE HELP IT CLEAR MY MANY DOUBTS ON MAINTANANCE PLANE


Thursday, February 06, 2014 - 3:10:34 PM - kavitha Back To Top

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

Great instructions - just what I needed.  Thanks


Wednesday, May 09, 2012 - 6:35:11 AM - kaviha Back To Top

 

 

Transactionlog backup job fail what are the reasons


Sunday, May 06, 2012 - 7:33:44 AM - Rene Back To Top

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

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


Learn more about SQL Server tools