Getting Error 14421 in SQL Server – Complete Solution is Here

Summary: This tutorial shows you how to stop the log shipping alert which is sending SQL Server error 14421. Apart from this, we will also discuss the reasons for error 14421 in SQL Server.

Problem 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 Error 1442 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 been checked the agent log and logshipping 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 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 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 error 14421 severity 16 state 1 in SQL Server, such as:

• Setting up incorrect value for the Backup Alert threshold
• Backup job is 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

SQL Server Error 14421, severity: 16, state: 1 – Solution

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

1. Check if the SQL Server Agent Service is operating for the secondary server instance. Also, check that the restore job in 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 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 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 permission 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 prior to running the alert message job. Here, the issue because of which this 14421 error is occuring is of the copy job as well.

Fix SQL Server Error 14421 by Focusing on the SQL Log Files

When we discus the log files of the SQL Server also known as LDF, we must focus on the alternative solution to fix the error code. Therefore users can trust the SQL Log Analyzer Tool. As we discussed earlier, the error can occur due to issues related to the log files, an advanced software can fix such issues without any hassles.

Download Now Purchase Now

The analyzer for the logs can work in plenty of ways for users. It can easily work for the fixing of damaged log files, lost log files, accidentally deleted log files, etc. Moreover, it offers plenty of advanced features comes along that fix other issues as well. In a nutshell, this way, users can solve all log related errors,use  data for auidts, legal & compliance, data sharing, restoration, etc services.

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 SQL Error 1442 with ease.