Transaction Log Shipping Fails with Log Sequence Number Gap

When This Error Shows Up

You'll see this after a log backup restore fails on the secondary server. The error message says something like "The log in this backup set begins at LSN ... which is too late to apply to the database." Or you get error 9002 — the transaction log is full on the secondary, and it can't accept more log records. Usually happens when the primary's backup chain gets broken — someone took a full backup manually, or a drive ran out of space mid-backup. The secondary database then sits there with a gap in its log sequence number (LSN), and every subsequent restore fails.

Root Cause

The root cause is almost always a missed log backup or a corrupted backup file in the chain. Log shipping relies on continuous log backups — each backup picks up where the last one left off. If a backup fails, or if someone takes a full backup without setting COPY_ONLY, the log chain breaks. The secondary can't apply a log backup that starts at an LSN it hasn't seen. The fix isn't complicated, but you can't just skip the missing piece. You need to realign the secondary.

Step-by-Step Fix

Step 1: Identify the Gap

On the secondary server, run this query to check the last restored LSN:

SELECT last_hardened_lsn FROM sys.dm_hadr_database_replica_states;
-- Or for older log shipping:
SELECT MAX(restore_history.last_lsn) FROM msdb.dbo.restorehistory;

Compare that to the latest log backup's first LSN. You can get that from the backup file name or use RESTORE HEADERONLY on the backup file. The gap is the difference.

Step 2: Take a New Full Backup (with COPY_ONLY)

Don't break the production chain. Use COPY_ONLY to take a new full backup of the primary database:

BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB_copyonly.bak' WITH COPY_ONLY;

Step 3: Restore the Full Backup on Secondary (WITH NORECOVERY)

Copy that backup file to the secondary server. Restore it with NORECOVERY:

RESTORE DATABASE [YourDB] FROM DISK = 'C:\Restore\YourDB_copyonly.bak' WITH NORECOVERY, REPLACE;

REPLACE is safe here because the database is already in restoring mode. It just overwrites the existing data files.

Step 4: Restart Log Shipping

Now the secondary database has a clean starting point. Resume the log shipping jobs. The first log backup after this full backup will have an LSN that matches the restore. The chain rebuilds from there.

Step 5: Verify Synchronization

After a few log backup cycles, check the status:

SELECT * FROM msdb.dbo.log_shipping_monitor_secondary;

Look for last_restored_latency_seconds — it should be under 60 seconds if everything's syncing fast.

If It Still Fails

If you're still getting errors after step 4, check these:

• Backup file permissions — the SQL Server service account on the secondary needs read/write access to the share. Don't assume it works. Test by copying a file manually.
• Network connectivity — can the secondary reach the primary's backup share? Try net use Z: \\PrimaryServer\BackupShare from the secondary.
• Log shipping jobs — check the SQL Agent job history for the log shipping copy and restore jobs. Look for errors like access denied or file not found.
• Disk space — error 9002 means the log file on the secondary is full. Even after restore, the log can fill up if restores are lagging. The full backup and restore resets this, but a full transaction log on the primary can also cause issues. Shrink the log on the secondary if needed, but don't make a habit of it — it fragments the log.

One more thing: if the primary database is large (over 500 GB), the restore in step 3 might take hours. Plan for that. The secondary is offline during the restore. If you can't afford downtime, consider using Availability Groups instead of log shipping. But for most cases, this fix works fine.