SQL Server log shipping out of sync: restore job failing, LSN gaps and how to re-seed the secondary

#Symptoms

• Alert 14421: "The log shipping secondary database ... has restore threshold of N minutes and is out of sync."
• Alert 14420: "The log shipping primary database ... has backup threshold of N minutes and has not performed a backup log operation."
• The LSRestore_<primary>_<db> job fails repeatedly, or succeeds but restores nothing.

Log shipping is three Agent jobs: backup (primary), copy (secondary), restore (secondary). Find which one broke.

#Step 1: Status overview

On the monitor server (or primary/secondary if no monitor):

EXEC master.dbo.sp_help_log_shipping_monitor;

On the secondary, details per database:

SELECT secondary_database, last_copied_file, last_copied_date,
       last_restored_file, last_restored_date, last_restored_latency
FROM   msdb.dbo.log_shipping_monitor_secondary;

And the job history messages, which hold the real error:

SELECT TOP (20) j.name, h.run_date, h.run_time, h.run_status, h.message
FROM   msdb.dbo.sysjobhistory h
JOIN   msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE  j.name LIKE 'LS%'
ORDER  BY h.instance_id DESC;

Log shipping's own detailed log:

SELECT TOP (50) log_time, message
FROM   msdb.dbo.log_shipping_monitor_error_detail
ORDER  BY log_time DESC;

#Step 2: Backup job failing (primary)

#Step 3: Copy job failing (secondary)

Usually permissions or network: the secondary's Agent service account must read the primary's backup share, and write the local copy folder.

# Run as the secondary's SQL Agent service account (or check with its credentials)
Test-Path \\sqlprimary\LSBackup\SalesDB
Get-ChildItem \\sqlprimary\LSBackup\SalesDB | Sort-Object LastWriteTime -Descending | Select -First 5

#Step 4: Restore job failing: the LSN chain is broken

The most common restore error:

The log in this backup set begins at LSN 41000000045600001, which is too recent to apply to the database.
An earlier log backup that includes LSN 41000000044800001 can be restored.

Something took a log backup outside log shipping (a backup tool, a maintenance plan, a DBA running BACKUP LOG to free space). That backup went somewhere else, so the secondary is missing a piece of the chain.

Find it on the primary:

SELECT bs.backup_start_date, bs.first_lsn, bs.last_lsn, bmf.physical_device_name, bs.user_name
FROM   msdb.dbo.backupset bs
JOIN   msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE  bs.database_name = 'SalesDB' AND bs.type = 'L'
  AND  bs.backup_start_date > DATEADD(day, -2, GETDATE())
ORDER  BY bs.backup_start_date;

Look for a file whose path isn't the log shipping folder, with an LSN range covering the gap.

#Fix: restore the missing file manually, then let the job continue

Copy that file to the secondary and restore it:

RESTORE LOG SalesDB
FROM DISK = N'E:\Recovery\SalesDB_extra_log.trn'
WITH NORECOVERY;      -- or STANDBY = N'E:\LSStandby\SalesDB.tuf' if the secondary is readable

Then run the restore job; it picks up from the next file.

Tip

If the out-of-band backup was COPY_ONLY, it doesn't break the chain at all, and the cause is something else. Ask backup tool owners to use COPY_ONLY (or exclude log-shipped databases from their log backups).

#Other restore errors

#Step 5: When you must re-seed

If the missing log backup is gone for good, or the secondary was recovered (WITH RECOVERY) by mistake, the chain can't be repaired. Options:

Differential bridge (faster): if a full backup taken before the gap is already restored on the secondary, a differential from the primary taken now can bridge it:

-- Primary
BACKUP DATABASE SalesDB TO DISK = N'\\sqlprimary\Seed\SalesDB_diff.bak' WITH DIFFERENTIAL, COMPRESSION;

-- Secondary
RESTORE DATABASE SalesDB FROM DISK = N'\\sqlprimary\Seed\SalesDB_diff.bak' WITH NORECOVERY;

This works only if the secondary's last restored point is based on the same full backup the differential is based on (same differential base). Otherwise:

Full re-seed:

-- Primary
BACKUP DATABASE SalesDB TO DISK = N'\\sqlprimary\Seed\SalesDB_full.bak' WITH COMPRESSION, CHECKSUM;

-- Secondary
RESTORE DATABASE SalesDB FROM DISK = N'\\sqlprimary\Seed\SalesDB_full.bak'
WITH NORECOVERY, REPLACE,
     MOVE 'SalesDB'     TO N'F:\Data\SalesDB.mdf',
     MOVE 'SalesDB_log' TO N'G:\Log\SalesDB_log.ldf';

Keep the log shipping jobs disabled while the full restore runs, then enable them; the next log backups after the full apply normally.

Warning

RESTORE ... WITH REPLACE overwrites the secondary database. Double-check you are connected to the secondary server before running it.

#Step 6: Check thresholds and alert job

SELECT secondary_database, restore_threshold, threshold_alert_enabled
FROM   msdb.dbo.log_shipping_monitor_secondary;

Make sure the LSAlert_<server> job is enabled and SQL Agent alerts for 14420/14421 notify an operator.

#Prevent it next time

• Exclude log-shipped databases from any other log backup process, or force COPY_ONLY.
• Keep backup file retention longer than your longest expected secondary outage.
• Use a monitor server and route alerts 14420/14421 to on-call, not just the Agent log.
• Practise a DR failover and a re-seed once a year so the steps are familiar.