There are few errors we may encounter with Log Shipping and most of them can be easily resolved. To get them resolved, we can get help from job history. Let's see this with the help of an example.
Question 1: Why do we get the following error?
Log Shipping is Out of Sync
We have to dig the SQL Server Error Log and Job History to get more information. Follow these steps to troubleshoot the error:
1. Expand the Job Activity Monitor, right-click on the job, and click View history.
2. As we can see in the error message here, it says that the T-Log restore job has failed to restore the T-Log and is out of sync for the last 59 minutes.
3. We have to carefully see what is causing this job to trigger the error, as this error caused by restoration hasn't occurred for 59 minutes. Let's check the backup job, to see if the backup job was performed successfully before. When checking job history of the backup job, I noticed that the backup job failed because it could not find the network path where we have kept the backup files (see the following screenshot).
4. The next course of action is to check what has caused the network error. Some of the possible reasons could be:
These are just an example and there could be some other possible reasons too. We have to carefully analyze them to troubleshoot the error.
Question 2: I get the following error:
The log in this backup set begins at LSN xxxxxxxxxxxxxxxx, which is too late to apply to the database. An earlier log backup that includes LSN xxxxxxxxxxxxxxxx can be restored.
Or, the following error at times:
Error: The log in this backup set begins at LSN xxxxxxxxxxxxxxxx, which is too early to apply to the database. An earlier log backup that includes LSN xxxxxxxxxxxxxxxx can be restored.
Answer: The main reason for this error to come up is the T-Log getting truncated. There are a few steps we need to perform to resolve this error.
1. We have to carefully check the last log backup file that was restored on the Secondary Server.
2. Disable all the Log Shipping jobs.
3. Check if the immediate backup file is there in the backup folder. If it is, restore that last backup file with the "No Recovery" or "Standby" option.
4. Enable all the Log Shipping jobs.
Even the following error is possible:
Could not apply log backup file "path\file" to secondary database "db", exclusive access could not be obtained because the database is in use.
The error is self explanatory. It says that while applying a T-Log backup, the SPID could not obtain the exclusive access, which is required to restore database/log backup.
Answer: We should check the disconnect users in the database when restoring backups option. This will disconnect the users while restoring the backup file.
Question 3: My primary server goes down. How can I make my secondary database as primary?
Answer: There are a few steps we have to perform manually to make secondary database a primary one.
1. Check if we can have Tail Log backup of the primary database.
2. If we are able to take Tail Log backup, apply the tail log backup with Recovery option while restoring T-Log backup.
3. If the primary database doesn't allow us to take backup, execute the following command:
restore database <dbname> with recovery4. We also need to ensure that the logins and other metadata are in sync with the secondary database.