The call came on a Saturday morning: the disaster recovery site was nineteen hours behind, and nobody had noticed. The log shipping setup on the order database had been "rock solid for three years" — the phrase the infrastructure lead used, twice — until a developer ran an ad-hoc log backup to local disk at 14:07 on Friday to capture a before-and-after around a data fix. That single backup took the log chain with it, because the shipped copy on the network share no longer contained the contiguous sequence the secondary needed. The restore job did not fail; it simply found nothing it could restore, logged success, and went back to sleep. The alert that should have fired was configured with the defaults: alert when the restore is more than the threshold out of sync, with the threshold left at a value so generous that nineteen hours of drift sat comfortably inside it. A full rebuild from a fresh full backup — 1.4 terabytes over a WAN link — was the Saturday and most of Sunday.
Log shipping is the oldest high-availability trick in the SQL Server book — take log backups, copy them, restore them — and precisely because it is simple and old, it collects operational debt in ways nobody documents. This is the field guide to the gotchas that actually produce incidents: what breaks the chain, why jobs report success while falling behind, how to measure the real lag, and when to stop babysitting it. Applies to SQL Server 2016 through 2022, and honestly to every version still running it.
What actually breaks a log shipping chain?
The chain breaks when the sequence of transaction log backups becomes discontinuous, and the classic cause is a log backup taken outside the shipping schedule — an ad-hoc BACKUP LOG by a developer, a third-party backup tool that includes log truncation in its nightly job, or a second log shipping configuration someone forgot to remove. Log shipping does not own the log; it is a consumer of a sequence that anything on the primary can interrupt. The developer's Friday backup is the textbook case: it was a perfectly valid log backup, taken to a local path, never copied to the share, and the secondary's restore job needs exactly that file to continue. The fix in the moment is unglamorous — find the missing backup file, copy it into the incoming share, let the restore job chew through it — and it is only possible if the ad-hoc backup still exists. When the file is gone, the chain is gone, and the answer is a full or differential backup restore on the secondary to re-baseline, which for a big database is the rebuild. The prevention is process, not tooling: BACKUP LOG requires permissions most logins should not have, and any backup software in the environment must be configured copy-only or to leave the log chain to shipping. A COPY_ONLY full backup, for the record, is safe — full backups do not break log chains at all; only log backups do, which is a fact worth stating plainly because half the team believed the opposite during the incident review.
Why did the restore job report success while falling behind?
Because the restore job's job is to restore every file it finds that is newer than its last restored point and in sequence, and "found nothing eligible" is a successful run, not an error. When the chain breaks, the incoming share still fills up — the copy job keeps copying happily, since copying files does not care about log sequence numbers — and the restore job wakes up every fifteen minutes, scans the directory, finds no file it can apply, writes a success history entry, and exits. The secondary's status in the log shipping monitor looks calm. The pile of unrestored .trn files growing in the share is the only visible symptom, and nobody graphs directory file counts by default. The same success-while-stuck behavior appears when a restored file's sequence leaves a gap the job will wait on forever, and when the restore job was disabled "for the maintenance weekend" and never re-enabled — disabled jobs also produce no failures, which is why my audit checks job enabled state as a fact, not just job success. The alert that exists — the out-of-sync threshold on the monitor server — only works when someone sets it to a number that means something, and the default alert thresholds are a suggestion, not a posture.
How do I measure the real lag instead of trusting the jobs?
You measure from the data, not the job history: the log shipping monitor tables in msdb record the last copied and last restored file with timestamps, and the difference between the newest backup on the primary and the last restored point on the secondary is the actual recovery-point exposure. The query I keep in the runbook runs against the monitor server (or each side, if there is no monitor):
SELECT primary_database,
last_backup_date,
DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS backup_lag_minutes
FROM msdb.dbo.log_shipping_monitor_primary;
SELECT secondary_database,
last_restored_date,
last_restored_latency,
DATEDIFF(MINUTE, last_restored_date, GETDATE()) AS restore_lag_minutes
FROM msdb.dbo.log_shipping_monitor_secondary;Three numbers tell you where the pipeline is stuck: backup lag (primary not producing or the backup job dead), copy lag (files exist on the primary share but not the secondary — network or copy job), and restore lag (files present on the secondary, not applied — chain break or restore job). Each has a different owner and a different fix, which is why "log shipping is behind" is a useless page and "restore lag is 1,140 minutes with copy lag at zero" is an actionable one. Alert on restore lag against your actual recovery point objective — if the RPO is thirty minutes, the threshold is thirty minutes, not the shipped default — and alert on the backup job's success independently, because a dead backup job produces no files and therefore no lag in any file-based view until it is far too late. The broader discipline of proving your recovery path instead of assuming it is the same one as in my backup restore verification notes, and the reason the log itself keeps growing when restores stall connects to the VLF behavior in my transaction log notes.
When do I rebuild the secondary instead of letting it catch up?
You rebuild when the time to find and re-apply the missing sequence exceeds the time to re-baseline from a full backup — and you make that decision with arithmetic, not optimism. A secondary behind by a broken chain at 14:07 Friday needs every log backup since its last restore, in order, present in the share. If the ad-hoc file is findable, that is minutes of work: drop it in, watch the restore chew through a day's backlog, done. If the file is gone, the catch-up option does not exist no matter how long you wait, and every hour spent hoping is an hour the 1.4-terabyte copy could have been running. The other rebuild trigger is quieter: a secondary that has been restoring WITH STANDBY for years accumulates operational scars — orphaned users, a standby file that has grown odd, restores that take longer than the backup interval produces them — and a periodic planned rebuild during a calm week is cheaper than an unplanned one during an incident. Rebuild is a normal maintenance operation for log shipping, not an admission of failure.
Is log shipping still worth running against availability groups?
Yes, for the jobs AGs do not do: cross-domain and cross-network DR where a cluster cannot stretch, a reporting copy you want read-only with controlled staleness, migration staging where you cut over after a final tail-log restore, and cheap warm standby for databases whose RPO of fifteen minutes is genuinely fine. Log shipping survives things that frighten clustered solutions — it is three SQL Agent jobs and a file share, every piece inspectable, every failure mode mechanical. The trade is that everything in this article is your responsibility: the chain, the thresholds, the rebuild drills. An AG moves failure detection and failover into the platform; log shipping leaves them in your runbook, and the runbook needs the drills to stay true. Teams that treat log shipping as install-and-forget get the Saturday I got; teams that alert on the three lags, restrict BACKUP LOG, and rebuild on a schedule get three more years of "rock solid."
Watching log shipping with MonPG when SQL Server support lands
The signals worth graphing here are backup, copy, and restore lag as three separate series rather than one mushy "shipping health," the count of unrestored files in the incoming share as an early-warning derivative, and job enabled state as a configuration fact that pages when it changes. A restore lag trending up with copy lag flat is a broken chain, and the alert should say so in those words. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, schedule the monitor queries above, set your restore-lag threshold to your real RPO, and take away everyone's ad-hoc BACKUP LOG permissions before next Friday.