SQL Transaction Log Shipping fails to restore database to standby

Question

I have set up transaction log shipping between 2 SQL 2014 Servers, everything seems to setup correctly but when the restore happens it seems to fail if the .trn is really small eg 7k.

Not sure if that has any bearing on it but it the only thing that is different.

Here are the logs from the restore job.

Date 25/04/2016 22:59:24 Log Job History (LSRestore_IRIS_WebStock)

Step ID 1 Server HERA Job Name LSRestore_IRIS_WebStock Step Name Log shipping restore log job step. Duration 00:00:04 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent
Operator Paged Retries Attempted 0

Message 2016-04-25 22:59:28.71 Error: Could not apply log backup file 'E:\ShippingLogs\WebStock\WebStock_20160425033000.trn' to secondary database 'WebStock'.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.71 Error: An error occurred while processing the log for database 'WebStock'. 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 'WebStock' (12: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 'WebStock', file 'WebStock' on file 1. Processed 1 pages for database 'WebStock', file 'WebStock_log' on file 1.(.Net SqlClient Data Provider) 2016-04-25 22:59:28.71 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Skipping log backup file 'E:\ShippingLogs\WebStock\WebStock_20160425033000.trn' for secondary database 'WebStock' because the file could not be verified. 2016-04-25 22:59:28.73 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: Could not apply log backup file 'E:\ShippingLogs\WebStock\WebStock_20160425034500.trn' to secondary database 'WebStock'.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Skipping log backup file 'E:\ShippingLogs\WebStock\WebStock_20160425034500.trn' for secondary database 'WebStock' because the file could not be verified. 2016-04-25 22:59:28.73 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteScalar requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) 2016-04-25 22:59:28.73 Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 2016-04-25 22:59:28.73 Error: Could not apply log backup file 'E:\ShippingLogs\WebStock\WebStock_20160425040000.trn' to secondary database 'WebStock'.(Microsoft.SqlServer.Management.LogShipp

If I remove that log and run the restore again it works until it finds another log that is really small.

Would the restore fail if the log was empty?

Answer 1

From dataavail,

The log in this backup set begins at LSN <#>, which is too recent to apply to the database

This is an error which is seen in various log shipping production scenarios.

2016-07-25 07:37:12.34 * Error: The file ‘C:\LS_Secondary\LogShippingDB_20160725020411.trn’ is too recent to apply to the secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) *

2016-07-25 07:37:12.34 * Error: The log in this backup set begins at LSN 79000000014400001, which is too recent to apply to the database. An earlier log backup that includes LSN 79000000011200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *

Log shipping works on the concept that each transaction backup log forms a chain with the previous transaction log backup. If we try to skip any of the log backups, then above error would be encountered. To find our missing backup, we can use MSDB backup history tables or ERRORLOG file. Both of them contain information about backup type, location etc.

2016-07-25 07:32:11.95 Backup Log was backed up. Database: LogShippingDB, creation date(time): 2016/07/24(21:53:30), first LSN: 79:48:1, last LSN: 79:80:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\LS\LogShippingDB_20160725020211.trn’}). This is an informational message only. No user action is required.

2016-07-25 07:33:11.83 Backup Log was backed up. Database: LogShippingDB, creation date(time): 2016/07/24(21:53:30), first LSN: 79:80:1, last LSN: 79:112:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\LS\LogShippingDB_20160725020311.trn’}). This is an informational message only. No user action is required.

2016-07-25 07:33:32.22 Backup Log was backed up. Database: LogShippingDB, creation date(time): 2016/07/24(21:53:30), first LSN: 79:112:1, last LSN: 79:144:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Extra.trn’}). This is an informational message only. No user action is required.

2016-07-25 07:34:11.69 Backup Log was backed up. Database: LogShippingDB, creation date(time): 2016/07/24(21:53:30), first LSN: 79:144:1, last LSN: 79:176:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\LS\LogShippingDB_20160725020411.trn’}). This is an informational message only. No user action is required.

We have highlighted the log backup which is failing. Now, we need to go back in time and find out why the previous log didn’t get restored on the secondary server.

Solution:

Find the missing log backup and restore it manually in the secondary database. Once restored, the next backups will catch up automatically.

Answer 2

http://sqlmag.com/database-high-availability/how-restart-failed-log-shipping-quickly

Please read the article.. there's more to it. I think i can't post the whole article here.

It's All About the LSN

The LSN, or Log Sequence Number is the trail of bread crumbs that allows any recovery process in SQL Server to know the order of transactions to be applied. All recovery processes need to occur in this specific order to ensure that transactions are read from the transaction log and log backup files in such a manner that they were applied to the primary database originally.