SQL Server Error 14421 Log Shipping Restore Job Failed
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 log shipping monitor information.
The above 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.
Table of Content
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.
Also Read: Remove Fragmentation in SQL Server Database
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 log shipping errors in SQL server 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. Solving this issue can improve performance slow running SQL server database.
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 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.
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 have to close systems for a while & restart it. After performing this operation, the error will get resolved and now user can continue with there work.