Select Page

How To Fix Log Shipping Failure Errors In SQL Server

Author: Eric Russo | | August 10, 2016

Log shipping is a very old technology solution provided by the SQL Server product to achieve high availability. This feature works under the transaction log backup. The backups are taken from the primary database, shipped to the secondary server, and restored on the secondary database on a regular basis. The frequency of the backup, copy, and restore can be defined while doing log shipping configuration and can be changed later, easily, if needed.

Advantages Of Log Shipping In SQL Server

One of the advantages of log shipping technology is that you can define more than one server, and this server can be in a secondary role. You can also have a monitored server, which can record the history and the status of the backup, copy, and restore operations and, optionally, raise alerts if these operations fail to occur as per the defined schedule.

Let’s look at some potential log shipping failure errors you could receive, their explanations, and some suggested solutions.

Log Shipping Failure Error #1: Exclusive access could not be obtained because the database is in use.

Here is the log shipping failure message which can sometimes be seen in the restore job of SQL Server log-shipping.

2016-07-24 21:58:32.42  *** Error: Could not apply log backup file ‘C:LS_SecondaryLogShippingDB_20160724162612.trn’ to secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) ***
2016-07-24 21:58:32.42  *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Here are the configurations that will create this error in log shipping:

  1. Secondary is configured as read-only.
  2. There were connections to the database when restore was attempted.
  3. The option “disconnect users…” is unchecked, as shown below.

Database Settings

The log shipping failure error itself is not a problem unless it is causing the restore process to halt. If this happens across an extended period of time, you’ll want to step in to fix it.

Solution: Based on the business situation, you can enable the checkbox.

Log Shipping Failure Error #2: The log in this backup set begins at LSN <#>, which is too recent to apply to the database

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

2016-07-25 07:37:12.34  *** Error: The file ‘C:LS_SecondaryLogShippingDB_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 you try to skip any of the log backups, then above error would be encountered. To find your missing backup, you 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:LSLogShippingDB_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:LSLogShippingDB_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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackupExtra.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:LSLogShippingDB_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.

Log Shipping Failure Error #3: Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.)

The above log shipping failure error is part of the error received during restore of the transaction log backup. Below is the complete message taken from restore job history.

2016-07-25 07:59:12.37     *** Error: Could not apply log backup file ‘C:LS_SecondaryLogShippingDB_20160725022811.trn’ to secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) ***

2016-07-25 07:59:12.37     *** Error: Directory lookup for the file “C:NewLocationLogShippingDB_log_new1.ldf” failed with the operating system error 2(The system cannot find the file specified.).

File ‘LogShippingDB_log_new’ cannot be restored to ‘C:NewLocationLogShippingDB_log_new.ldf’. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

2016-07-25 07:59:12.43     *** Error: The log backup file ‘C:LS_SecondaryLogShippingDB_20160725022811.trn’ was verified but could not be applied to secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) ***

The log shipping failure error is caused when a new file is added to the primary database and the path of new file doesn’t exist on a secondary server.

Solution: There are two ways to solve the log shipping failure error mentioned above:

  1. Create the same path on the secondary server where the restore is failing.
  2. If it’s not possible to recreate the path on the secondary server, then you need to restore the failing log manually using “WITH MOVE” and move the new file to a valid location on the secondary server.

There might be other causes of log shipping failure, including network issues, but these are the most commonly observed ones, each of which can be fixed by the data base administrator.

Datavail Script: Terms & Conditions:

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

Datavail SQL Server Consultants
At Datavail, we have more than 1,000 database professionals on our team, including experts in SQL Server. We provide scalable and affordable support for all of your database administration needs. For more information, please call (866) 834-8954.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS