DBA Scripts: Get Log Shipping Status

Which Log Shipping Job Stopped

Log shipping copies transaction-log backups from a primary to a secondary. If the secondary falls behind or alert 14420 or 14421 fires, run this on an instance with the monitor rows: the script compares backup, copy and restore ages with their configured thresholds and shows which step needs attention. Here, status means the monitored log-shipping chain, not general server health.

Where Each Age Comes From

Log shipping is 3 SQL Server Agent jobs that fail on their own, and each one writes its own timestamp to a monitor table in msdb:

• Backup job (primary) writes last_backup_date to log_shipping_monitor_primary. If this is old, the primary has stopped taking log backups and nothing downstream can move.
• Copy job (secondary) writes last_copied_date to log_shipping_monitor_secondary. Old here but current on the primary means the files exist and are not arriving.
• Restore job (secondary) writes last_restored_date to the same table. Old here but current on the copy means the files arrived and are not being applied.

Copy current and restore stale is the restore job. Copy stale as well is upstream: the backup job or the share. The verdict column makes that comparison for you, against the thresholds set when log shipping was configured, not numbers chosen by the script. Ages are worked out from the UTC columns, the same way the alert job counts them, so servers in different time zones do not skew them.

If You Arrived Holding Error 14420 or 14421

These are the 2 alerts the log shipping alert job raises, and they point at different ends of the chain:

• 14420, “has backup threshold of N minutes and has not performed a backup log operation for N minutes”. This is the backup job on the primary. The secondary is not the problem yet; it has run out of things to restore.
• 14421, “has restore threshold of N minutes and is out of sync. No restore was performed for N minutes. Restored latency is N minutes”. This is the secondary, but it does not say which job. MS Docs notes that a restore job which succeeds with “Could not find a log backup file that could be applied” does not update the restore time, and that the cause may then be the copy job.

So 14421 on its own is ambiguous, and copy_age_min is the column that resolves it. Read that one first.

When to Run This Script

• The moment a 14420 or 14421 alert lands, before opening job history
• When someone reports the reporting secondary is showing stale data
• After any change to the backup share, the Agent service account, or the network between primary and secondary
• After patching or restarting either instance, to confirm all 3 jobs picked back up
• Routine SQL Server health checks on any instance that holds a log shipping role

The Script

Run it on the instance that holds the log shipping monitor role. If you are not sure which that is, run it on the secondary first.

/*
Script Name : Get-LogShippingStatus
Category    : high-availability
Purpose     : Show every log shipping pair on this instance and how far behind each secondary is.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : SELECT on msdb.dbo.log_shipping_monitor_primary and _secondary (db_datareader in msdb)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  WHAT THIS ANSWERS, mid-incident: "is the secondary behind, and which part of the chain
  stopped - backup, copy, or restore?"

  Log shipping is three jobs, not one, and they fail independently:
     backup  on the primary    -> msdb.dbo.log_shipping_monitor_primary.last_backup_date
     copy    on the secondary  -> log_shipping_monitor_secondary.last_copied_date
     restore on the secondary  -> log_shipping_monitor_secondary.last_restored_date

  Reading only "restore latency" tells you the secondary is behind but not WHY. If the copy
  date is current and the restore date is old, the restore job is the problem. If the copy date
  is also old, look further up: the backup job or the share. That is the whole diagnostic, and
  it is why this script prints all three ages side by side rather than one lag number.

  WHERE TO RUN IT. The monitor tables are populated on whichever instance holds the monitor
  role - often the secondary, sometimes a third server. A row appears here only for the parts
  this instance monitors, so an empty result is not proof that log shipping is healthy. It may
  mean you are on the wrong instance. The status row below says so rather than returning
  nothing and letting you conclude the wrong thing.

  THRESHOLDS ARE THE CONFIGURED ONES, not invented. backup_threshold and restore_threshold are
  the values set when log shipping was configured, in minutes. This compares against those
  rather than against a number of my choosing.

  AGES ARE UTC, the same way the alert job counts them. sys.sp_check_log_shipping_monitor_alert
  compares the *_utc columns with GETUTCDATE(), so a primary or secondary in another time zone
  from the monitor does not skew the ages. The last_* columns shown are the local times.

  LATENCY COUNTS TOO. The 14421 alert also fires when last_restored_latency (minutes from the
  log backup being taken to it being restored) passes the restore threshold, even when restores
  are running on time. The verdict checks it as well.
*/

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.log_shipping_monitor_primary)
   AND NOT EXISTS (SELECT 1 FROM msdb.dbo.log_shipping_monitor_secondary)
BEGIN
    SELECT 'No log shipping rows on this instance. Either log shipping is not configured, or '
         + 'this server does not hold the monitor role - check the primary and the secondary.'
           AS status;
END
ELSE
BEGIN

    SELECT
        'PRIMARY'                                              AS role,
        p.primary_server                                       AS server_name,
        p.primary_database                                     AS database_name,
        NULL                                                   AS partner_server,
        p.last_backup_date                                     AS last_backup,
        DATEDIFF(MINUTE, p.last_backup_date_utc, GETUTCDATE()) AS backup_age_min,
        p.backup_threshold                                     AS backup_threshold_min,
        NULL                                                   AS last_copied,
        NULL                                                   AS copy_age_min,
        NULL                                                   AS last_restored,
        NULL                                                   AS restore_age_min,
        NULL                                                   AS restore_latency_min,
        NULL                                                   AS restore_threshold_min,
        CASE
            WHEN p.last_backup_date_utc IS NULL THEN 'NO BACKUP RECORDED'
            WHEN DATEDIFF(MINUTE, p.last_backup_date_utc, GETUTCDATE()) > p.backup_threshold
                 THEN 'BACKUP LATE'
            ELSE 'OK'
        END                                                    AS verdict
    FROM msdb.dbo.log_shipping_monitor_primary AS p

    UNION ALL

    SELECT
        'SECONDARY',
        s.secondary_server,
        s.secondary_database,
        s.primary_server + '.' + s.primary_database,
        NULL,
        NULL,
        NULL,
        s.last_copied_date,
        DATEDIFF(MINUTE, s.last_copied_date_utc, GETUTCDATE()),
        s.last_restored_date,
        DATEDIFF(MINUTE, s.last_restored_date_utc, GETUTCDATE()),
        s.last_restored_latency,
        s.restore_threshold,
        CASE
            WHEN s.last_restored_date_utc IS NULL THEN 'NOTHING RESTORED YET'
            -- copy current but restore stale: the restore job is the failure, not the network
            WHEN DATEDIFF(MINUTE, s.last_restored_date_utc, GETUTCDATE()) > s.restore_threshold
                 AND DATEDIFF(MINUTE, s.last_copied_date_utc, GETUTCDATE()) <= s.restore_threshold
                 THEN 'RESTORE JOB BEHIND (copy is current)'
            -- both stale: the problem is upstream of the restore
            WHEN DATEDIFF(MINUTE, s.last_restored_date_utc, GETUTCDATE()) > s.restore_threshold
                 THEN 'BEHIND - copy is stale too, check the backup job and the share'
            -- restoring on time, but each file is applied long after it was taken (14421 fires)
            WHEN s.last_restored_latency > s.restore_threshold
                 THEN 'RESTORE LATENCY OVER THRESHOLD'
            ELSE 'OK'
        END
    FROM msdb.dbo.log_shipping_monitor_secondary AS s
    ORDER BY role, database_name;

END

It returns 1 row per primary database and 1 per secondary, with each job’s age in minutes graded against its own configured threshold. When both monitor tables are empty it returns a status row instead of an empty grid.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Show every log shipping pair and which job is behind:
.\run.ps1 Get-LogShippingStatus

# To run against a remote sql server:
.\run.ps1 Get-LogShippingStatus -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/high-availability/logshipping/Get-LogShippingStatus.sql

Example Output

With no log shipping on the instance, the whole result is one status row:

An empty grid on the wrong instance looks exactly like a healthy one, which is why the script says so instead. If you get this row on a server you know is in a log shipping pair, check you are connected to the instance you think you are. The primary and secondary keep their own monitor rows, so on either of them the tables should not be empty.

Understanding the Results

Primary rows carry only the backup columns and secondary rows only the copy and restore columns, so the unused cells are NULL by design. partner_server pairs each secondary back to its primary. The columns that decide what you do next:

NOTHING RESTORED YET and NO BACKUP RECORDED mean the configuration exists but the job has never written a timestamp. On a pair set up an hour ago that is expected. On one that has run for months, the job has never succeeded or the monitor row was reset.

What To Do With Each Verdict

BACKUP LATE. Go to the primary. Check SQL Server Agent is running and the backup job (category Log Shipping Backup) is enabled and on schedule, then read its history for the failing step. MS Docs names an invalid backup folder path and a full disk as possible causes.

RESTORE JOB BEHIND (copy is current). Stay on the secondary and open the restore job’s history. A file it cannot apply usually means someone took a log backup outside log shipping on the primary and broke the chain, or the next file in sequence is missing from the copy folder. If the job succeeds with “Could not find a log backup file that could be applied”, find the missing file before anything else; if it is gone, the secondary needs re-initialising from a fresh full backup.

BEHIND - copy is stale too. Do not start with the restore job. Check the primary row first: if it reads BACKUP LATE, fix that and the chain catches up on its own. If the primary is fine, the copy job is the fault: the share unreachable from the secondary, or the copy job’s account losing read access to it.

RESTORE LATENCY OVER THRESHOLD. Restores are running but applying old files. Check the secondary’s restore delay against its restore threshold first; if there is no delay, the restore job is working through a backlog.

Whatever the verdict, once the failing job runs again, re-run the script and watch the ages drop. The alert job only raises when a threshold is passed; it does not tell you it has recovered.

Best Practices

• Know which server is the monitor before the incident. If the monitor role is on the primary and the primary is what just died, the monitor tables went with it.
• Set both thresholds to at least 3 times the job frequency, as MS Docs recommends, or every schedule slip becomes an alert and the alert stops meaning anything.
• With a deliberate restore delay, set the restore threshold above the delay, or the delayed restore reads as behind by design.
• Keep the log backup chain to the log shipping backup job alone. A one-off log backup taken elsewhere is the classic way a healthy restore job suddenly cannot find a file to apply.

MS Docs: log_shipping_monitor_primary · log_shipping_monitor_secondary · sp_help_log_shipping_monitor · About log shipping

Related Scripts

You may also find these useful:

• High Availability (hub)
• Backups and Recovery (hub), every backup and restore script
• Backup Failed or the Restore Is Stuck, when the backup job itself is failing on the primary
• Last Restore History, what the secondary actually applied and when
• Backup Chain Integrity, for the out-of-band log backup that breaks a restore job
• Database Backup History, to find a log backup taken outside the log shipping job
• The Log in This Backup Set Is Too Recent or Too Early to Apply, the error a restore job hits when the chain is broken
• Cannot Open Backup Device: Operating System Error 5, when a job cannot reach the share
• SQL Agent Job Failure Summary, the log shipping jobs are Agent jobs and fail like any other
• Database Cannot Be Opened, It Is in the Middle of a Restore, the state a log shipping secondary lives in
• Mirroring Endpoint Health and Status
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

A secondary that is behind has 3 possible causes on 2 different servers. This script puts the backup, copy and restore ages side by side against their configured thresholds and names the job to open first. Run it on the monitor server the moment a 14420 or 14421 lands, and again after the fix to watch the ages fall. An empty result is the wrong instance, not a healthy pair.