By: Greg Robidoux | Comments (10) | Related: > 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... |
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
- If your transaction log backup jobs have been failing check this out to see if this is the issue
- If you use maintenance plans, always use the logging feature and make sure it is part of your daily routine to check these logs
- Check out these other tips about Maintenance Plans
- Check out these tips about other ways to get database info
About the author
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
