3

I have a problem with Transaction Log Shipping on the SQL Server 2014 edition. I have to log ship 4 databases, 2 of them are fine and the other 2 ALWAYS fail but not at first. When it fails, SQL reports that one of the log backups in the chain "could not be verified" thus breaking the chain. These 2 databases in question will also end up in a "Restoring" state. All the articles that I have read about this issue report that I should put the database in emergency mode and run a DBCC CheckDB, but I can't since the database is in restoring state...

Additionally, I have verified that the broken log backup in question is valid by running a RESTORE with VERIFYONLY, and no errors are reported.

I have set up log shipping on these two databases up at least 7 times now, always to have it break in a few hours. What can cause this behavior?

Here are some of the logs. This first one is the last successful restore after setting log shipping up again:

2016-11-23 15:41:53.66 Restored log backup file. Secondary DB: 'XXX', File: 'XXX.trn'

And then the very next message is

2016-11-23 15:41:55.29    *** Error: Could not apply log backup file 'XXX.trn' to 
secondary database 'XXX'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: An error occurred while processing the log for database
'text.staging.fsintel.com'.  
If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Both instances are on SP2 GDR.

  • 1
    Have you tried manually restoring the backups/logs to a different server? Does the log chain break in the same spot? – AMtwo Apr 9 '17 at 18:01
  • You could try taking a differential backup on Primary, restoring that to your log ship secondary with norecovery, then try to resume applying logs. That would confirm if the log chain is simply broken, or if the secondary database is broken. – AMtwo Apr 9 '17 at 18:02
  • Any events logged into windows error logs or sql server error logs ? – Kin Shah Aug 7 '18 at 18:23
  • Could you please provide schedule of full, differential and log backup at primary server and also schedule of log backup, copy backup and restoration of backup through log-shipping job. – Learning_DBAdmin Sep 11 at 5:45
0
An error occurred while processing the log for database 

'text.staging.fsintel.com'. If possible, restore from backup. If a backup is not 

available, it might be necessary to rebuild the log.

Somehow your logs are getting miss matched.

  1. If you have any Log backups job please disable it.
  2. Avoid taking log backups while enabled log shipping, or just manually run the logshipping backup job for log backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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