Error message 14420 and error message 14421 that occur when you use log shipping in SQL Server
If you are using SQL Server Log Shipping then above error message number will be familiar.
One of the following error messages may be logged in the SQL Server error log:
Error message 14420
Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.
Error message 14421
Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.
Still there is a refinement in the error message (not solution) to understand :
Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.
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.
A brief note on major change in the Log Shipping between 2000 and 2005 versions is, within SQL 2000 version log shipping uses Sqlmaint.exe to back up and to restore databases. The usual BACKUP LOG statements are passed that creates a transaction log backup as part of a log shipping setup. Also this Sqlmaint.exe process connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. A change of process when you perform a COPY or RESTORE job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.
More often I see within forums & newsgroups users asking for solution when it occurs in their environment, thinking it is a problem in log shipping. Actually it is not, as a part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity, so you will need to set the backup-alert & out-of-sync thresholds depending upon the backup interval between primary and secondary servers.
The message 14220 refers the difference between current time and time that indicates the last_backup_filename value stored on LOG_SHIPPING_PRIMARIES table within the Log Shipping Monitor server, greater than value that is set for the Backup Alert threshold. Similarly message 14221 indicates the time difference between the last_backup_filename in LOG_SHIPPING_PRIMARIES table and last_loaded_filename in LOG_SHIPPING_SECONDARIES table, which will be greater than the value set for the Out of Sync Alert threshold.
Microsoft documentation & BOL indicates that both of these messages does not necessarily indicate a problem with log shipping, but still if it occurs then you must check the schedule of log backup & restore. As the message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Within my experience I see this occurs due to the time difference between Primary & Secondary server including the Log Shipping Monitor server, if you have setup on seperate instance. Also if there is any issue within the Log Shipping MONITOR server such as you restart it during any hotfix or patching of operating system, then the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. Recently within a DR based SQL Server I have seen that one of the transaction log backup job has failed due to the some non-logged operations and DBA executing BACKUP LOG ... WITH NO_LOG within the scripts causing the
backup job on the primary server is failing and to resolve this I have checked the job history for the backup job to see a reason for the failure.
Similarly the message number 14421 does not necessarily indicate a problem with Log Shipping, as it refers that difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly. 8 out of 10 times I see this message is generated due to the problems within the BACKUP job and mostly during the COPY job from Primary & Secondary servers, causing to result in "out of sync" alert messages. Say if you have checked the job of backup & copy of file between servers has not issues then it is nothing bu network connectivity during copy job to fail.