Select Page

3 Common Log Shipping Failure Errors a DBA Can Fix

Eric Russo | | August 10, 2016

Log Shipping Failure

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.

One of the advantages of this technology is that we can define more than one server, and this server can be in a secondary role. We 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 errors you could receive, their explanations, and some suggested solutions.

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

Here is the 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_Secondary\LogShippingDB_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 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.

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

This is another 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.

Error #3: Directory lookup for the file <location> failed with the operating system error 2(The system cannot find the file specified.)

The above 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_Secondary\LogShippingDB_20160725022811.trn’ to secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) ***

2016-07-25 07:59:12.37     *** Error: Directory lookup for the file “C:\NewLocation\LogShippingDB_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:\NewLocation\LogShippingDB_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_Secondary\LogShippingDB_20160725022811.trn’ was verified but could not be applied to secondary database ‘LogShippingDB’.(Microsoft.SqlServer.Management.LogShipping) ***

The 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 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 we 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 DBA. Have you seen other errors I haven’t mentioned here? What were they and what solution did you use?

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

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