Log shipping secondary is behind its threshold

What this check looks for

Two separate questions, both answered from the log shipping monitor tables in msdb.

On the secondary side, msdb.dbo.log_shipping_monitor_secondary: a database whose last_restored_date is null, or is older than its own restore_threshold in minutes.

On the primary side, msdb.dbo.log_shipping_monitor_primary: a database whose last_backup_date is null, or is older than its own backup_threshold in minutes. A secondary cannot be current if the primary has stopped producing log backups, and that failure shows up first on the machine nobody is watching.

Rows with a threshold of zero are skipped, because zero means no threshold was configured rather than a threshold of nothing.

The number in each message is the threshold the log shipping configuration declared, not a value this report invented. A database listed here is late by the standard whoever set it up chose.

Why it matters

Log shipping fails quietly, and it fails in a specific pattern that makes it look healthy.

The copy job and the restore job are separate. The copy job keeps succeeding, so files keep arriving on the secondary and the folder keeps growing, which is the thing an administrator would notice. The restore job is the one that sits disabled or failing, and it produces no visible artefact at all.

The alert job exists precisely to catch this, and it is very often the first thing switched off, because it fires during every maintenance window and every network blip until somebody gets tired of it. From that point the only remaining signal is somebody opening the secondary and noticing the data is hours old, which normally happens during an incident, which is the worst possible moment to discover that the standby is not standing by.

The gap is also your real recovery point. A secondary six hours behind means six hours of data loss on failover, whatever the recovery plan claims.

How to confirm it yourself

SELECT s.[secondary_database],
       s.[last_restored_date],
       DATEDIFF(MINUTE, s.[last_restored_date], GETDATE()) AS [minutes_behind],
       s.[restore_threshold],
       s.[last_restored_file]
  FROM msdb.dbo.log_shipping_monitor_secondary AS s WITH (NOLOCK)
 ORDER BY [minutes_behind] DESC;

SELECT p.[primary_database],
       p.[last_backup_date],
       DATEDIFF(MINUTE, p.[last_backup_date], GETDATE()) AS [minutes_since_backup],
       p.[backup_threshold]
  FROM msdb.dbo.log_shipping_monitor_primary AS p WITH (NOLOCK)
 ORDER BY [minutes_since_backup] DESC;

Then look at the jobs themselves, because the monitor tables say what happened rather than why:

SELECT j.[name], j.[enabled], ja.[run_requested_date], ja.[last_executed_step_id],
       ja.[stop_execution_date]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysjobactivity AS ja WITH (NOLOCK)
         ON ja.[job_id] = j.[job_id]
 WHERE j.[name] LIKE 'LSRestore%'
    OR j.[name] LIKE 'LSCopy%'
    OR j.[name] LIKE 'LSBackup%'
    OR j.[name] LIKE 'LSAlert%'
 ORDER BY j.[name];

How to fix it

Work from the primary outwards, because a stalled primary makes every secondary look broken.

1. Is the backup job on the primary running? If last_backup_date is stale, nothing downstream can be current. Fix that first.
2. Is the copy job running, and is the share reachable? A changed service account password or an expired share permission stops the copy silently.
3. Is the restore job enabled? This is the single most common answer. Somebody disabled it for a maintenance window and it never came back.
4. Is the restore failing on a specific file? Read the job history. A missing log backup in the sequence breaks the chain, and the usual cause is an ad hoc log backup taken outside log shipping by a person or a third party backup tool.
5. Turn the alert job back on. If it was noisy, raise the threshold to a number that reflects what you actually promise, rather than switching off the only thing that was watching.

A chain broken by an out of band log backup has to be reinitialized from a fresh full backup. There is no way to splice it back together.

How long it takes

About an hour and a half. Most of it is catching up the backlog of log files once the restore job is running again.

Related reports

Related checks

Frequently asked questions

The restore job runs fine and this still fires. Then check the primary. A stale last_backup_date means the secondary is as current as it can be, and the problem is upstream.

The threshold seems wrong. It is whatever was configured when log shipping was set up, and defaults tend to survive for years. If 45 minutes is not your real tolerance, change the threshold to one that is.

Is a null last restored date the same as being behind? It is treated the same way and it usually means worse: the secondary has never restored anything, so log shipping was configured and never worked.

Why is the alert job mentioned when the check does not look at it? Because it is the reason nobody already knew. The check is doing the alert job’s work, so if this fires, that job is worth looking at too.