First time at posting a question on here, so please bear with me and I'll try and detail all the information.
Currently, I'm log shipping from SQL 2012 Standard Edition (11.0.3000) (lets call it ServerA) to another server in a data centre in a different domain (ServerDR) which is also SQL 2012 Standard Edition (11.0.3128). This log shipping is working fine and is reporting fine on the history.
When I then log ship from ServerA to another server (we'll call this ServerB) within the same network / domain I run into some issues. This is also SQL 2012 Standard Edition (11.0.3000), but ServerB is also used for Reporting duties. These only one of these databases will be reported against, the others are purely for a warm backup.
There is 7 databases being logshipped from ServerA to ServerDR and the same 7 databases are being logshipped from ServerA to ServerB. When logshipping from ServerA to ServerB, 6 of those databases will have issues when trying to restore the transaction log backup's, leaving the databases on ServerB in a restoring state, when I have set this to be left in standby / Read only mode.
Looking through the logs, this will happen. First part is the successful restore of the log, then the next will error:
2013-12-27 15:16:03.49 Restored log backup file. Secondary DB: 'Logship_xxxxx', File: 'F:\Logshipped Files\xxxxx_20131224171511.trn'
2013-12-27 15:16:05.82 * Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224171511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: An error occurred while processing the log for database 'Logship_xxxxx'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. An error occurred during recovery, preventing the database 'Logship_xxxxx' (27:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1.(.Net SqlClient Data Provider) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224173011.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224174511.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not apply log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' to secondary database 'Logship_xxxxx'.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2013-12-27 15:16:05.82 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2013-12-27 15:16:05.82 Skipping log backup file 'F:\Logshipped Files\xxxxx_20131224180010.trn' for secondary database 'Logship_xxxxx' because the file could not be verified. 2013-12-27 15:16:05.82 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) * 2013-12-27 15:16:05.82 *** Error: ExecuteNon
My thinking is, there is no issue with the transaction log backups, otherwise ServerDR would be suffering with the same issues. I have re-created the log ship jobs and restored a known good backup on ServerB, but it will break again.
One thing I have noticed though, is if I manually (and painfully) restore each transaction log backup, it still throws errors when using this T-SQL:
RESTORE LOG Logship_xxxxx
FROM DISK='F:\Logshipped Files\xxxxx_20131224164516.trn'
WITH STANDBY='F:\Logshipped Files\UNDO\UNDO_xxxxx_ARCHIVE.DAT'
Which will give an error of:
Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1. Msg 9004, Level 16, State 6, Line 1 An error occurred while processing the log for database 'Logship_xxxxx'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally. Msg 3414, Level 21, State 1, Line 1 An error occurred during recovery, preventing the database 'Logship_xxxxx' (27:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
But, if I keep working through each transaction log, sometimes I'll get:
Processed 0 pages for database 'Logship_xxxxx', file 'xxxxx' on file 1. Processed 1 pages for database 'Logship_xxxxx', file 'xxxxx_log' on file 1. RESTORE LOG successfully processed 1 pages in 0.042 seconds (0.034 MB/sec).
This will put the database back into standby mode, for it to only break again :-( I have also noticed that it doesn't break on the log shipping, when the transaction log files are larger than 10kb or so in size.
Any ideas on this one at all? Apologies for the long post and the somewhat iffy formatting.
RESTORE VERIFYONLYagainst the problematic log backup file? It sounds like that particular log backup file is corrupt.