Home » Log Shipping » Troubleshooting Log Shipping Issues in SQL Server

Troubleshooting Log Shipping Issues in SQL Server

👤 Naveen Sharma | Modified: May 8th, 2023|Log Shipping | 4 Minutes Reading

What is Log Shipping?

In SQL server, Log Shipping is a feature to automatically backup databases and transaction log files from a server (primary) database to a distinct (secondary) server. This backup file can be restored whenever end-user requires it. Moreover, it provides a solution for disaster-recovery and supports limited access (read-only) to copy initial database backup.
In this blog, we will talk on how to troubleshoot log shipping issues in SQL Server, but first let us discuss some log shipping errors occur in SQL Server.

Description of Log Shipping Errors in SQL Server

    • Error Message 14420

The destination of log shipping %s.%s is out of sync by %s minutes.

Troubleshooting Log Shipping Issues in SQL Server

    • Error Message 14421

The destination of log shipping %s.%s is out of sync by %s minutes.

sql-server-error-14421

    • Backup Error

At the primary server side, the backup message fails due to some disruption.

transaction-log-backup-error
transaction-log-backup-error-2

    • Restoration Error

At the secondary side of the server, the restoration procedure terminates because of some interruption.

Troubleshooting Log Shipping Issues in SQL Server

    • Error Caused Due to Machine Disk

At the production server side, the log shipping error continuously occurs while a user is backing up the databases or restoring it.

log-shipping-machine-disk-error

Troubleshooting Log Shipping Issues in SQL Server

Since we know that there are multiple errors caused by Log Shipping in SQL Server. There is a reason behind the occurrence of each error. Therefore, to troubleshoot log shipping in SQL Server, below-mentioned are the different solutions to resolve log shipping issues in SQL Server:

    • Solution 1: Troubleshoot Log Shipping Error 14420
      1. Examine whether the data and time of both primary and secondary servers are identical or not. If yes, then go to step (b), else modify them to make it identical and then proceed to the next step.
      2. Examine whether the monitor server is offline or not. If it is offline, then change it to online mode and wait for several minutes then go for the further processing or else go to step (c).
      3. Check that the authentication status between monitor server and primary server is complete.
      4. If the backing procedure fails, then analyze the job history to examine the reason behind the error occurrence. Moreover, resolve that error and then restart your system.

 

    • Solution 2: Troubleshoot SQL Server Log Shipping Error 14421
      1. Examine whether the data and time of both primary and secondary server are identical or not. If yes then go to step (b), else modify them to make it identical and then go to next step.
      2. Check that the authentication status between monitor server and primary server is complete.
      3. Examine the Copy job and the network connectivity because such error occurs due to improper connectivity between servers.
      4. If the restoring procedure gets terminated, then analyze the job history to examine the reason behind the error occurrence. Moreover, resolve that error and then restart your system.

 

    • Solution 3: Resolving Backup Error

The error occurs because the data is never backed up completely. Therefore, perform a full backup of primary database and its transaction log files for solving the issue.

 

    • Solution 4: Resolving Restoration Error

Apply NORECOVERY and REPLACE option, for restoring the backed up file on the secondary server. You can take help from the following snapshot to learn the usage of both these options.

fix-log-shipping-restoration-error

    • Solution 5: Resolving Error Caused Due to Machine Disk

There exist two solutions to handle this error and they are mentioned below:

    1. You may configure the log shipping feature again on all servers that will initialize your secondary database.
    2. This solution may involve the following scenario to be followed:
      • Terminate all operations of primary database
      • Move to SIMPLE model of recovery
      • Now switch to FULL recovery model
      • Generate a backup of complete database
      • At the end, start the Log Shipping operations

Conclusion

After going through the above-mentioned information, we can conclude with the fact that the trouble occurs only due to some small mistakes caused while performing operations like configuration, backing up and restoration. Therefore, to prevent the occurrence of such errors SQL server user should perform configuration with proper concentration. Moreover, if the error occurs, then this segment will guide users to troubleshoot log shipping issues in SQL Server.