Logshipping monitor incorrectly raises error number 14420 instead of 14421 when the secondary database is out of sync
This article helps you resolve the problem where Logshipping monitor incorrectly raises error number 14420 instead of 14421 when the secondary database is out of sync.
Original product version: Microsoft SQL Server
Original KB number: 2006312
Symptoms
Consider the following configuration of a Log Shipping environment:
- Server A hosts primary server instance and the database.
- Server B hosts secondary server instance and the database.
- Server C hosts a monitor server instance where in the Log Shipping Monitor job is configured to use an impersonated proxy account for the connections to Server A and Server B.
When you use this configuration, Log Shipping Monitor job incorrectly raises error message 14420 instead of 14421 when the secondary database is out of sync. The description of these error messages in SQL Server 2005 and SQL Server 2008 is as follows:
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.
The alert message 14221 indicates that the difference between current time (UTC) and the last_restored_date_utc
value in the log_shipping_monitor_secondary
table on the monitor server is greater than value that is set for the Restore Alert threshold whereas the alert message 14220 indicates that the difference between UTC and the last_backup_date_utc
value in the log_shipping_monitor_primary
table on the monitor server is greater than value that is set for the Backup Alert threshold.
Cause
The issue happens because of a problem in the Log Shipping user interface. When you create the monitor job for the secondary, 14220 is passed instead of 14221.
Resolution
To resolve the problem, correct the @threshold_alert
parameter value for the secondary database by executing the following statement on the monitor server (Server C):
use master
go
sp_change_log_shipping_secondary_database @secondary_database = 'dbname', @threshold_alert = 14421
More information
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for