Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking

Question

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

Answer 1

If the log backup's are able to be restored while the secondary database is in NORECOVERY and only fails when it is in READ-ONLY/STANDBY then I would assume the log backups themselves are ok and not corrupted.

It could be that your reporting component has an open connection to the database therefore when restoring the log file it is unable to obtain an exclusive connection to the database due to the open connections. There would be an option when setting up log shipping to disconnect any connections to allow it to restore the log backup.

Answer 2

On standby Secondary restore only disconnect users when job starts, after start users can connect ...and this will stop restore process with error about "exclusive acces" . I got service that tried to connect to standby database while in restore. That made restore process to break after 1-10/100 restored files.