Getting Error 14421 in SQL Server – Complete Solution is Here

Summary: This tutorial shows you how to stop the log shipping alert that is sending SQL Server error 14421. Apart from this, we will also discuss the reasons for error 14421 in SQL Server. Therefore, to understand the error better and find the best ways to resolve it, read the article to the end. However, before continuing with the solutions, let’s first understand what the error is about. 

Error Description

“We were in the middle of some pretty intense maintenance of SQL Server last night. Then, the primary database MDF file was badly fragmented. After that, we maintained a test copy of this database to test and set up log shipping on the test database. One of us deleted the test database even without removing the log shipping first. Now, when we are using the log shipping in SQL Server, it is throwing SQL Server Error 14421 message:

The log shipping secondary database SERVER.database has restore the threshold of 45 minutes and now it is out of sync. No restore was executed for 10022 minutes. The latency of restore is 0 minutes. We have checked the agent log and log shipping monitor information. Even we have removed everything and our research leads us to believe that this error is occurring due to the backup job. We are still trying to operate it, however, not getting any appropriate solution to overcome this error. Well, it is not a big deal but SQL Server error is prompting every couple of minutes in the log. Is there anything we can do to fix SQL Server error 14421, severity: 16, state: 1.? Thanks in advance!”

SQL Log Shipping Error 14421 – Possible Causes

SQL Server has a Log shipping feature that automates the backup of transaction log files on a primary database server then, restore them onto a standby server. Using this feature, one can restore the database at the time of disaster recovery.

Sometimes the log shipping feature disrupts the application functioning due to improper synchronization inside the server. This interruption occurs in the form of an error prompt that says ‘SQL Server error 14421 Severity 16 State 1’. It means some issues have occurred during the database recovery from backup file and the restoration process gets failed.

Apart from this, there are multiple other reasons that may result in error 14421 severity 16 state 1 in SQL Server, such as:

• Setting up incorrect value for the Backup Alert threshold
• Backup job failed on the primary server
• The restore job is not running
• Restore folder path is not valid
• Modified date/time on the monitor or primary server
• Data storage disk has no space
• log_shipping_primaries table not updated with current values
• Change in the schedule of the job
• Primary server is not connected to monitor server MSDB database
• Invalid entries in Windows registry
• Different timings of Monitor server and the primary server
• Unavailibility of the Agent is another major issue

Challenges Encountered Due to SQL Server Error 14421

• The risk of RPO is increased due to long restore latency creation.
• Ends up generating excessive alerts, further resulting in monitoring noise.
• Requires frequent and continuous manual intervention by Database Administrators.
• Often ends up detecting or indicating potential breaks or gaps in the log chains.
• Another possibility is that it can lead to SLA (Service Level Agreement) violations due to delays in synchronization.

SQL Server Error 14421, Severity: 16, State: 1 – Best Troubleshooting Ways

As discussed above that MSSQL Error 14421 does not only occur due to the log shipping failure, there may be several other reasons, like server or machine issues. Hence, below are some solutions that can resolve the log shipping error 14421 SQL Server:

1. Check if the SQL Server Agent Service is operating for the secondary server instance. Also, check that the restore job in the secondary database is enabled and scheduled for running at the suitable frequency.
2. As it is possible that the secondary server might be failing, hence, check the job history of the restore job to detect the main cause. If the permissions are not enabled then, enable them and restart your server.
3. The backup alert threshold value might be incorrect. Basically, this value should be set up to at least three times frequency of the restore job. Hence, if you have changed the frequency of the restore job when the log shipping is configured and starts functioning, update the backup alert threshold value accordingly.
4. Examine the current authentication status between primary server and monitor. In addition, check if all the permissions are configured properly. If not, then reconfigure them.
5. Update the monitor tables with latest data for your secondary database and run sp_refresh_log_shipping_monitor on the secondary server instance.
6. In case, monitor server instance goes offline and then returns online, and  log_shipping_monitor_secondary isn’t synced with the existing values before running the alert message job. Here, the issue because of which this 14421 error is occuring is with the copy job as well.

Best Practices to Prevent SQL Server Error 14421

Here are some of the prevention tips that will allow users to secure their databases from the occurrence of this error. 

• First practice to keep the database safe and secure of this error is to regularly monitor the log shipping jobs to detect any failures during operations. 
• Next, it is important to ensure that the log backup chain isn’t broken. 
• Check and verify the SQL Agent permissions to monitor updates, copy, and backups.
• In case the issue isn’t resolved and is creating further problems, it is better to reinitialize the log shipping process.

Conclusion

SQL Server Error 14421, Severity: 16, State: 1 in SQL Server defines that the value of Out of Sync Alert threshold is exceeded between previously backed up file and restore file. Therefore, we have discussed some possible and simple solutions that can troubleshoot SQL Log shipping error 14421 message quickly and seamlessly. Moreover, the manual commands like sp_refresh_log_shipping_monitor are also mentioned above for better results in fixing Error 14421 SQL Server with ease.