SQL Server Error 14421 Log Shipping Restore Job Failed

Jason Clark | July 27th, 2016 | SQL Server Error

In SQL server, while restoring database from a backup file user come across a very common error i.e. SQL Server error 14421

Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.

This message denotes that log shipping synchronization time is out i.e. the restoration threshold time has been expired. The restore threshold feature is the number in minutes, which passes between restore operations before generation of message.

Additional Information

SQL server automatically creates the backup of available database & LDF files, which is usually migrated from a primary server to secondary server. This feature of automatically creating backup files is known as Log shipping feature, by which one can restore data at the time of disaster recovery.

Sometimes due to improper synchronization within the server, log shipping feature interrupts the application functioning. This interruption occurs in form of error message dialog box that appears while working with the server. One such error is ‘SQL Server Error 14421 Log Shipping Restore Job Failed’, which clearly means that while recovering data from backup file some issues have occurred due to which restoration procedure is currently failed.

Possible Causes of the SQL Server Log Shipping Error 14421

Depending upon the Backup Alert threshold value and Out of Sync Alert value, such kind of error occurs. In addition, it is not important that this error message is occurred only due to problem with log shipping errors. However, there are many other possible causes due to which such error occurs in the server. Some of the causes are mentioned below:

  • Running Problem With Restoration: While restoring data from a backup file on secondary server, the restoration process may not be in active mode. In other words, the server where we are performing restoration procedure; it may be that restoration option is disabled. Therefore, such error occurs and restoration gets interrupted.
  • Restoration Activity Gets Failed: If the disk/storage device memory is full, then restoration procedure will get failed. Suppose user has specified wrong path (which does not exist on your machine) for restoration, then this SQL Server error 14421 will occur and hence, interruption while restoring database in the server.

Possible Solutions to Troubleshoot SQL Server Error 14421

Since earlier we have discussed that reason of this error occurrence is not only the log shipping feature of SQL server, but also there are many other reasons related to machine, server, etc. Such error defines that the value of Out of Sync Alert threshold has now exceeded between previous backed up file & restored file. Therefore, there will be different multiple solutions to troubleshoot this error. One should go for following solutions (one-by one) to troubleshoot SQL Server log shipping error 14421:

  • Analysis of Location: Check whether the location that you had specified exist or not, for restoration procedure. Now try to again restore the data and if the error continues, then go to next step; else stop the procedure here itself.
  • Examine the Permissions: Examine the setting of the server where you are restoring the database. Check whether the permissions are enabled for restoring the data or not. If it is enabled, then go to step (3); else enable the permission and restart your server properly.
  • Check Server’s Date & Time: Analyze whether the date & time of initial and secondary server is same or not. If not, then change these attributes; else continue with next procedure.
  • Determine Authentication Status: Another way to troubleshoot SQL Server error 14421 is by examining the current authentication status between monitor & primary server. In this status, you will have to check whether all permission are properly configured or not. If not, then reconfigure them; else go to next step.
  • Analysis of Job History: The operation performed earlier on the server should be checked. The previous activities performed on database can affect behavior of other features. Therefore, analyze your complete job history and if you find any issue through which the behavior is affected, then resolve that issue and restart your machine.

Observational Verdict

SQL Server error 14421 is very common error that occur in the server. Moreover, there are all possible solutions provided to troubleshoot error 14421 in SQL Server. In addition if the error does not gets resolved, then users will have to close there machine for a while and then restart it. After performing this operation, the error will get resolved and now user can continue with there work.

Transaction Log Restore