Saturday, December 27, 2014

Could not find a log backup file that could be applied to secondary database

Error: Could not find a log backup file that could be applied to secondary database 'Database Name'.(Microsoft.SqlServer.Management.LogShipping) 
Error: The file 'F:\Database_Transaction_Log\Database_20130405231501.trn' is too recent to apply to the secondary database ‘Database Name'.(Microsoft.SqlServer.Management.LogShipping)
Error: The log in this backup set begins at LSN 2045263000000026000001, which is too recent to apply to the database. An earlier log backup that includes LSN 2045263000000023900001 can be restored. Searching for an older log backup file. Secondary Database: 'Database Name'

A few of my clients encountered this issue recently and needed help. Basically, they've setup Log Shipping on one of their production databases to copy transactions to a secondary server (for reporting purposes). The setup was working fine except for the fact that Log Sipping will break sporadically with the above error messages, and they had to ‘reset’ Log shipping to resolve the issue. Just by looking at the error message, I made a guess that this must be due to transaction log backups that are being performed “outside” of Log Shipping – in other words, I thought there must be a process separate from Log Shipping that is performing transaction log backups. The reason this is going to be an issue is because when applying/restoring transaction log backups on to the secondary server, Log Shipping is not going to be able find the log backup that was taken “outside” of Log Shipping – this means that there will be a ‘break’ in the chain/sequence of transaction log backups and hence a successful restore won't be possible. Below are three different scenarios I ran into with different clients.

Client/Scenario #1: This scenario was simple and it was easy to identify and resolve the issue: this client had a maintenance Job (outside of Log Shipping) that was backing up the transaction log on a regular basis. We simply disabled the maintenance job and the issue got resolved.

Client/Scenario #2: In this scenario, there was nothing within SQL Server and outside of Log Shipping that was backing up the transaction log. After a couple of discussions, I found out that they are using Avamar for backups and recovery purposes so I obviously wanted to know if they are using Avamar for transactions log backups. The answer was “No, We just do full backups”. But I wanted to confirm so we took a closer look at some of the settings in Avamar and found one that says “Force incremental backup after full backup” under the “Options” tab in the “Edit Dataset” window (shown in screenshot below). It sounded like this option does transactions log backups but to be 100% sure, we reached out to Avamar technical support and they confirmed that the setting below DOES transactions log backups. The support also said that if you include a database in Avamar’s backup plan, by default, it will perform transaction log backups as well (if the database is in FULL recovery model). 

Long story short, we deselected the option "Force incremental backup after full backup" so Avamar stopped performing  transaction log backups and Log Shipping continued to work without any issues.

Client/Scenario #3: This scenario is pretty much the same as that of Scenario #2, except that this client uses Veeam for backup and recovery. As in scenario #2, we explored some of the options in Veeam and found a similar setting (though not as obvious) called “Enable application-aware image processing” as shown in the screen shot below (the one on the left). Veeam technical support did confirm that this setting performs full as well as transaction log backups but if we just need to do full backups (without transactions log backups), we need to deselect the option “Backup logs periodically” under the advanced settings shown in the screen shot below on the right.

So we left the option “Enable application-aware image processing” selected but deselected “Backup logs periodically” and that took care of the issue with Log Shipping.

Even if you are not using Log Shipping, it is not a good idea to have two different process backup the transaction log of a given database as it can cause issues and confusion at the time restoring those backups. I hope this is helpful. If you've experienced this error and the reason/resolution was different than what I posted above, please leave a comment with your solution. Thanks.

1 comment:

  1. Hi,

    We have developed a new gen developer friendly BI framework with some extremely unique features. Would like to give you early access & love to hear your opinion. Please do let me know of how to reach out to you. Would be launching product in 3 weeks from now.