Troubleshooting Common Log Shipping Issues

In this section, we'll look at few of the common problems that can occur in a log shipping environment and their solutions.

Exercise 70: Problem 1 – Middle of a Restore Error

Consider the following error:

Msg 927, Level 14, State 6, Line 9
Database 'Sales' cannot be opened. It is in the middle of a restore.

This issue occurs when the log shipping is configured in Standby mode with the Disconnect users at restore operation option enabled.

When a user transaction tries to read from the Standby secondary database, and at that time the restore job is restoring the transaction log, the user transaction terminates with this error.

Setup

To simulate the error, follow these steps:

1. Execute the following query to change the database state of the Sales database at the secondary DPLHA, from NoRecovery to Standby mode. The query is the same as mentioned in the Changing the Database State at the Secondary section:

   -- To be run at the secondary - DPLHA
   -- Get the restore...