Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on these databases isn't actually creating LOG backup files.

After doing some searching, it had been suggested to change database modes from FULL to SIMPLE, then back to FULL to reset things. After doing this I am still unable to get a LOG backup to successfully run.

I am using SQL Server 2005, and use the ola hallengren method of backup found here http://ola.hallengren.com/sql-server-backup.html.

Thank you

UPDATE: I figured it out!

Because the ola hallengren scripts check for log shipping roles and skip log backups if it finds the current db in either

msdb.dbo.log_shipping_primary_databases

or

msdb.dbo.log_shipping_secondary_databases

AND because the databases I have been fighting with recently DID have old log shipping entries that were not proiperly cleaned out, this script was not able to create the LOG backups. Now that that is all cleaned up, we appear to be good to go.

Many thanks to all who took the time to chime in! -Josh

share|improve this question
    
Do you have an error or anything that hints at why the log backups aren't running? –  Kenneth Fisher Oct 22 '13 at 22:05
    
The ola hallengren method of doing backups has fairly verbose reporting. On a successful LOG backup, it will show you the following: –  Josh Haley Oct 22 '13 at 22:25
1  
In the future it's easier to add additional information (particularly large amounts) to your post by editing it rather than multiple comments. –  Kenneth Fisher Oct 22 '13 at 23:23
1  
Please tell us exactly what error message you get when you run the following code manually: BACKUP LOG databasename TO DISK = 'C:\somepath\somefile.trn' WITH INIT; –  Aaron Bertrand Oct 23 '13 at 0:25
1  
Have you checked the error log? Under Management > SQL Server Logs. If the log backup is failing it'll tell you in there. And if you switched recovery modes did you take a full backup afterwards to start the log chain? –  georgeb_dba Oct 23 '13 at 15:05
show 7 more comments

2 Answers

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize:

  • In order for a log backup to be useful for a restore, there needs to be a database backup along with log backups that cover all the LSNs since the time of that database backup.

  • When you change the recovery model to simple, SQL Server breaks the log backup chain. Then when you change it back to full, the database continues to act like it is in simple mode until someone creates a new log backup chain by taking a database backup. This is known as pseudo-simple mode. The reason this is implemented this way is to prevent someone from taking log backups that could never be used for recovery because they aren't part of a log backup chain that begins with a database backup.

  • Once a database backup is taken, a new log backup chain begins and the database begins functioning in the full recovery model. At this point your log backups should work, or at least fail for another reason with a more descriptive error.

This will be at least one reason why your log backups aren't functioning now.

share|improve this answer
    
Thank you James. That was a very insightful link and explanation. I installed the sp and used it on one of my trouble databases and it gave me a "1", meaning really in full recovery mode. I am still unable to take a log backup of the database. I inherited these servers and dbs and there were log shipping policies in place at one point. Those policies have since been deleted, but I fear the databases may still be expecting to be a part of log shipping (see my earlier comments). I am unable to even access the log shipping tab in SSMS 2005 for this one database I am focusing on. –  Josh Haley Oct 22 '13 at 23:56
    
I don't think the log shipping configuration will cause your log backups to fail. Have you run a database backup since setting change the recovery model back to full? What error do you get if you type out a BACKUP LOG command in SSMS? –  James Oct 23 '13 at 0:07
    
When I use: BACKUP LOG [DATABASENAME] TO DISK = N'Z:\Backups\test_log.trn' WITH INIT; It works. But when I use the ola hallengren method (which normally works) it doesn't work. That code is: EXECUTE [dbo].[DatabaseBackup] AT-Databases = 'DATABASENAME', @Directory = N'Z:\Backups', AT-BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y' –  Josh Haley Oct 23 '13 at 22:49
    
I updated the OP with what I found. I figured it out. Thanks for the help. Not sure how to end a post like this as answered so I will just mark this answer. Thanks! –  Josh Haley Oct 24 '13 at 16:31
1  
I believe you can create your own answer. Self-answering is probably the best fit here since the problem ended up being unrelated to what I wrote about. –  James Oct 24 '13 at 20:17
show 1 more comment
up vote 1 down vote accepted

UPDATE: I figured it out!

Because the ola hallengren scripts check for log shipping roles and skip log backups if it finds the current db in either

msdb.dbo.log_shipping_primary_databases

or

msdb.dbo.log_shipping_secondary_databases

AND because the databases I have been fighting with recently DID have old log shipping entries that were not proiperly cleaned out, this script was not able to create the LOG backups. Now that that is all cleaned up, we appear to be good to go.

Many thanks to all who took the time to chime in! -Josh

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.