Sql-server – Log Shipping always fails, after a while

log-shippingsql serversql server 2014

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.

Best Answer

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.