Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

Log shipping error 14421: the restore threshold is out of sync (and how 14420 differs)

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Error 14421 is an alert, not a restore failure by itself. It says that the monitor has no record of a restore on a log shipping secondary database for longer than the restore threshold. The restore job may have stopped, may be failing, may have restored fine while the monitor was not told, or the threshold may simply be too tight for the schedule.

Error 14420 is the same alert for the primary side: no log backup was recorded within the backup threshold. Work out which side raised the alert, read the monitor tables, and fix the one cause the tables point to. Do not change thresholds until you know the jobs are healthy.

What the error means

Microsoft documents the 14421 message text as: "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 restore threshold is "the number of minutes that can elapse between restore operations before a message is generated."

The 14420 text reads: "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." Both pages state that the message "does not necessarily indicate a problem with log shipping."

Log shipping runs three jobs: a backup job on the primary, a copy job on each secondary and a restore job on each secondary. An optional monitor server records their history. Microsoft documents that if a monitor server is configured, one alert job runs on the monitor and raises errors for all operations. Without a monitor, the primary watches the backup and each secondary watches its own copy and restore.

The alert works from the restore time recorded in the monitor tables. A secondary whose monitor row is stale can therefore alert even though its restores are running, and the threshold_alert_enabled setting decides whether the alert is raised at all.

Likely causes

Cause What you see How to confirm
Restore job not running No restore history after a point in time SQL Server Agent service on the secondary is stopped, the job is disabled or its schedule was changed
Restore job failing Job history shows failed runs Job history on the secondary names the error: invalid restore folder path, full disk or any other RESTORE failure
Copy job not delivering files Restore succeeds with "Could not find a log backup file that could be applied to secondary database." and the restore time is not updated Compare last_copied_date with the last backup on the primary; read copy job history
Monitor not updated Jobs succeed but monitor rows are old Authentication problem between secondary and monitor, or the monitor was offline and back
Threshold too tight Alert fires although every restore succeeds restore_threshold is smaller than about three times the restore job interval
Wrong clock Microsoft says a wrong date or time "may also generate alert messages" Date or time differs on the secondary or monitor

Prove the cause before changing anything

All of these are read-only. Start on the monitor server, in the master database. Microsoft documents that sp_help_log_shipping_monitor must be run from master on the monitor server and needs sysadmin membership or execute permission on the procedure. With no monitor, use sp_help_log_shipping_secondary_database on the secondary instead (below).

EXEC master.sys.sp_help_log_shipping_monitor;

The result set has one row per registered database. Read these columns: status (0 means healthy with no agent failures, 1 means otherwise), is_primary (1 for a primary row), time_since_last_backup, time_since_last_copy, time_since_last_restore, last_restored_latency, restore_threshold and is_restore_alert_enabled. A large time_since_last_copy with a small gap between copy and restore points at the copy job. A large time_since_last_restore with a recent copy points at the restore job.

For one secondary, still on the monitor, run:

EXEC master.sys.sp_help_log_shipping_monitor_secondary
    @secondary_server = N'sql02',
    @secondary_database = N'Sales';

The page documents restore_threshold, threshold_alert, threshold_alert_enabled, last_copied_file, last_copied_date, last_restored_file and last_restored_date. On the secondary itself, sp_help_log_shipping_secondary_database (run from master, sysadmin only) returns the settings, including copy_job_id, restore_job_id, monitor_server and restore_delay.

Then read the jobs. Microsoft says to confirm that SQL Server Agent is running on the secondary, that the restore job is enabled and scheduled, and to "check the job history for the restore job to look for the cause." Open the history in SSMS under SQL Server Agent. The monitoring page also lists log_shipping_monitor_error_detail and log_shipping_monitor_history_detail in msdb for error and session detail. Microsoft does not show a sample of good or bad output for these tables, so judge by the columns above: a good row has a recent last_restored_date and a latency inside the threshold.

Finally, compare the clocks on the primary, secondary and monitor. Microsoft notes that different time zones are not a problem, but a wrong date or time is.

Fix

Agent stopped or job disabled. Start the SQL Server Agent service on the secondary, enable the restore job and check its schedule.

Risk: a restore job that has been off for a while now has a backlog of log files to apply and will read and write heavily until it catches up. This cannot be undone, but it only moves the secondary forward. Run it outside peak hours if the disks are shared.

Restore job failing. Fix what the history names: correct the restore folder path, free disk space, or deal with the RESTORE error text. Do not restore log files by hand into the secondary database without a plan, because the secondary then no longer matches the job's own record of what it applied.

Copy job not delivering. Read the copy job history, check the backup share is reachable from the secondary and that the copy job's destination folder is the one the restore job reads. Microsoft says the "Could not find a log backup file" case "might be an issue with the copy job."

Monitor not updated. After correcting the authentication problem between the secondary and the monitor, or after the monitor returns, refresh the monitor tables. Microsoft documents sp_refresh_log_shipping_monitor for this, run from master on the secondary. For a restore agent the arguments are the secondary ID as @agent_id, @agent_type = 2 and @mode = 1. The secondary ID is the secondary_id column of log_shipping_monitor_secondary.

EXEC master.sys.sp_refresh_log_shipping_monitor
    @agent_id = '<secondary_id>',
    @agent_type = 2,
    @mode = 1;

Risk: @mode = 2 deletes monitor information instead of refreshing it, and there is no way back. Use @mode = 1 only.

Threshold too tight. Microsoft says to set the threshold "to at least three times the frequency of the restore job" and to update it if you change the job frequency after setup. First read the current settings with sp_help_log_shipping_secondary_database. Then change it on the secondary, from master, with sp_change_log_shipping_secondary_database. Microsoft marks @restore_delay, @restore_all, @restore_mode, @disconnect_users, @restore_threshold and @threshold_alert_enabled as not accepting NULL, so supply the current values for every setting you are not changing. The values in the block below are placeholders: replace each with the value you recorded, and set @restore_threshold to the figure you have decided on.

EXECUTE master.dbo.sp_change_log_shipping_secondary_database
    @secondary_database = N'Sales',
    @restore_delay = 0,
    @restore_all = 1,
    @restore_mode = 0,
    @disconnect_users = 0,
    @restore_threshold = 45,
    @threshold_alert_enabled = 1;

Disruptive step: this changes the live log shipping configuration, including how the restore job behaves. Wrong values for @restore_mode or @disconnect_users change the state of the secondary or disconnect readers. Back out by running the procedure again with the values you recorded first. Microsoft documents a default of 14421 for @threshold_alert. Read the current threshold_alert value from sp_help_log_shipping_monitor_secondary before you change anything, and do not alter it as part of a threshold change.

Clocks. Correct the date and time on the instance that is wrong, then refresh the monitor as above.

Risk: changing a server's clock affects more than log shipping. Kerberos authentication, SQL Server Agent job schedules and certificate validity all depend on the time. Impact: a manual jump can make jobs run twice or be skipped, and can break sign-ins to the monitor. Back-out: note the current date and time before the change, and prefer re-syncing from the domain or the organisation's time source rather than typing a time by hand. Setting the clock back to the old wrong value is the only way back.

Example (illustrative): sql01 ships Sales to sql02, with a monitor on sql03. The restore job runs every 15 minutes, at 10, 25, 40 and 55 minutes past each hour, so three times that interval is 45 minutes. Someone set restore_threshold to 30. The last restore finished at 09:00 (the 08:55 run). The SQL Server Agent service on sql02 stops for a patch reboot at 09:05 and returns at 09:35, and the restore job's next run is at 09:40. That is a 40-minute gap between restores (09:40 minus 09:00). With the threshold at 30, the monitor raises 14421 once 30 minutes have passed without a restore, around 09:30. Nothing is broken: the alert is accurate for the threshold set, and a threshold of 45 would have absorbed the 40-minute gap. After the change to 45, check that last_restored_latency falls back to a few minutes once the job has caught up.

Confirm the fix

Run sp_help_log_shipping_monitor on the monitor again. The status column should be 0 for the database, time_since_last_restore should be inside restore_threshold, and last_restored_file should have advanced. The Log Shipping Report in SSMS shows the same figures. Wait through at least two restore job cycles before you call it fixed.

What to watch afterwards

  • Alert history: the alert job on the monitor, not the secondary, raises the error when a monitor exists. Check that the alert reaches an operator.
  • Any change to restore job frequency. Update the threshold at the same time.
  • Disk space on the secondary's restore folder and the primary's backup share.
  • Agent restarts after patching, which interrupt both copy and restore.
  • Monitor changes: Microsoft states the monitor server can't be changed without removing log shipping first.

Risks and limits

These commands have not been run or lab-tested. Microsoft's 14421 page is dated 2023-03-01 and is written for SQL Server generally; confirm behaviour on your version. The About log shipping page notes that monitoring can break when a remote monitor runs SQL Server 2025 (17.x) while other instances in the topology run an earlier version, and that the workaround is to drop and recreate the configuration, which is a larger change than anything above.

A 14421 alert tells you about the monitor's record, not about the data. A secondary that is hours behind is not a usable standby: log shipping does not fail over by itself, and a secondary only gets as current as its last restored log.

NetLeverage includes a daily full disaster-recovery backup of each hosted server at no extra charge. Talk to an engineer.

Sources