We are using log shipping and RESTORE WITH STANDBY
on SQL Server 2012 in order to restore the database in read-only mode for reporting purposes. However, the log shipping setup keeps breaking after completing a restore of one or two log backups. Log-shipping only breaks when it is running as RESTORE WITH STANDBY
; RESTORE WITH NORECOVERY
does not cause any problems.
My only intuition about this is that the primary database is not that dynamic. Therefore, when there are no transactions, this causes issues with the RESTORE
process, maybe?
Any ideas, known fixes?
I had it working for a few days by running a regular job that does heavy updating on two tables. When the job stopped running the log shipping setup quickly failed, unable to process the .trn file. I reset log-shipping and tried to see if it would keep running by just doing a small update, changing the value of one column of one record in a table, whoever it still failed.
Thanks for all your responses.
PS: An excerpt from our log
02/25/2013 13:00:00,LSRestore_DBDB01-A_BulldogDB,In Progress,1,DBREPORTS,LSRestore_DBDB01-A_BulldogDB,Log shipping restore log job step.,,2013-02-25 13:00:12.31 *** Error: Could not apply log backup file '\\dbsan01\DBBackups\LSBackup_BulldogDB\BulldogDB_20130225180000.trn' to secondary database 'BulldogDB'.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.31 *** Error: An error occurred while processing the log for database 'BulldogDB'. 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 'BulldogDB' (8: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 'BulldogDB' file 'BulldogDB' on file 1. Processed 1 pages for database 'BulldogDB' file 'BulldogDB_log' on file 1.(.Net SqlClient Data Provider) *** 2013-02-25 13:00:12.32 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.32 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.32 Skipping log backup file '\\dbsan01\DBBackups\LSBackup_BulldogDB\BulldogDB_20130225180000.trn' for secondary database 'BulldogDB' because the file could not be verified. 2013-02-25 13:00:12.32 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.32 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.33 *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.33 *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.33 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.33 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.33 *** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.33 *** Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.33 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.33 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) *** 2013-02-25 13:00:12.33 Deleting old log backup files. Primary Database: 'BulldogDB' 2013-02-25 13:00:12.33 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) *** 2013-02-25 13:00:12.33 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***,00:00:12,0,0,,,,0