Log Shipping - Database Health Monitor

Overview

The Log Shipping report answers how far behind every chain on this instance is, and – the part that turns a number into an action – which of the three agents is the reason.

Log shipping is a three-stage relay: backup on the primary, copy to the secondary, restore on the secondary. When a chain falls behind, the useful question is never “how late”, it is “which stage stopped”. Those are three different problems with three different fixes, and a single timestamp cannot tell them apart.

The chart draws all three stage timestamps on a shared time axis, so a stalled copy agent, a failing restore agent and a backup job that stopped on the primary have three different shapes rather than three identical dates.

What this fixes

The old page was two flat grids behind a pair of hand-drawn radio buttons, and it was thin in ways that mattered:

There was no lag anywhere on the page. You were handed a timestamp and left to do the date arithmetic in your head – twice, once per tab.
last_copied_date was never queried at all, so the middle stage of a three-stage process did not appear in a report named after it.
Neither configured threshold was read, so the page had no opinion about whether any of it mattered.
An instance with no log shipping got two empty grids and no explanation, because the only code path that would have said so was never called.
The radio buttons were hard-coded gray and stayed light gray in dark mode, and six error messages still named the Email Alerting report the page had been copy-pasted from.

The Log Shipping report — The whole report. Every chain, with all three relay stages on one axis.

The relay chart — Backup, copy and restore on a shared time axis – the gap between the dots is which agent stopped.

Where to find it

An instance-level report. Right-click the server in the tree and open Instance Reports → Log Shipping.

The report reads whichever sides this connection can see, and says which those were. If the instance is only the primary, the page tells you so rather than showing a blank column and letting you assume the worst.

Reading the chart

Each chain is a row with three dots on a shared time axis – one per relay stage. The distance between them is the delay introduced by that stage, so the shape of the row names the culprit:

What you see	What it means
All three dots close together	Healthy. The relay is keeping up.
Backup dot far left, other two close behind it	The backup job on the primary stopped. Nothing downstream can do anything about it.
Backup current, copy dot lagging	The copy agent is stalled – usually a share, a permission, or disk on the secondary.
Backup and copy current, restore dot lagging	The restore agent is failing – or the standby is deliberately delayed.

The bands

Every leg falls into one of six bands:

Band	Meaning
Sync	Comfortably inside its allowance – half of it or less.
Within	Behind, but still inside the threshold configured for it.
Breach	Past the threshold. Somebody’s promise about this chain is no longer true.
Broken	Far past it (three times the allowance or more), or an agent has reported an error, or nothing has ever been restored.
Delayed	Behind on purpose – `restore_delay` accounts for it. This is not a verdict.
Unknown	Stages this connection cannot see. Not the same fact as being current.

Delayed and Unknown are the two bands worth understanding. A deliberately delayed standby is a supported configuration, often used as protection against a bad change propagating – and the old report would have called it broken. Unknown means the report could not see that stage at all, which is very different from seeing it and finding it healthy.

The Threshold view

The second view divides each leg’s lag by the threshold that leg was actually configured with. That is the only way a fifteen-minute chain and a four-hour chain can be ranked against each other honestly – being ten minutes late means something very different to each.

Use Relay to diagnose one chain. Use Threshold to rank all of them.

Reading the grid

The log shipping grid — One row per leg, with the lag computed rather than left as arithmetic for the reader.

Column	What it is
Chain	The shipped database.
Role	Whether this instance is the primary or the secondary for that chain.
Status	The verdict, including which agent is being blamed and why.
Behind By	The lag, computed.
Allowance	The threshold this leg was configured with.
Last Backup	When the log was last backed up on the primary.
Last Copied	When it was last copied to the secondary. The stage the old report never queried.
Last Restored	When it was last restored.
Restore Mode	Standby or norecovery.
Secondary Server	The other end.

Status and Behind By come first so they are on screen without scrolling. Restore Mode and the secondary server can sit past the edge on a small screen; hover over a row to see them.

A leg that has never restored anything sorts to the top with the worst rather than the bottom with the unreadable ones. It has no lag, but it is not fine.

Right-click a leg for its history

Right-click a row, or a chain on the chart, to see what actually happened to the database:

Backup History for database – on the primary. Opens the database’s backup list, which is every backup msdb holds for it, so you can see the log backups the chain is shipping.
Restore History for database – on the secondary. Opens Restore History with that database’s restores selected and scrolled into view, so you can see the log restores that have been applied.

Which one you get depends on the instance you are connected to. Backups are recorded in the msdb of the server that took them and restores in the msdb of the server that ran them, so the page offers the side that lives on this instance. Connected to a separate monitor server, which holds neither, the menu shows the item greyed out and says to connect to the primary or the secondary.

Right-click a leg to change its settings

The same menu changes the settings of a chain that can be changed without setting log shipping up again, on whichever side of the chain lives on the instance you are connected to:

Change Restore Mode of database to STANDBY / NORECOVERY… – on the secondary. Switches the restore mode in one step. The mode is read again first, so the switch is from what the server holds now, and the confirmation says what the change means (STANDBY leaves the secondary readable between restores and needs the readers out of the way; NORECOVERY leaves it restoring) and shows the one statement it will run.
Change Log Shipping Settings… – opens a dialog with everything else.

Side	What the dialog changes
Primary	The backup folder and share; how many minutes backup files are kept; backup compression; the backup alert threshold, the alert number, whether the alert is raised, and how long history is kept; and the backup job: how often it runs, the hours it runs between, and whether the job and its schedule are enabled.
Secondary	The source and destination folders; how many minutes copied files are kept; the restore mode, whether readers are disconnected, the restore delay, whether every waiting file is restored, and the block size, buffer count and max transfer size; the restore alert threshold, alert number, alert on or off and history; and the copy job and restore job, each with its frequency, hours and enabled flags.

Side

What the dialog changes

Primary

The backup folder and share; how many minutes backup files are kept; backup compression; the backup alert threshold, the alert number, whether the alert is raised, and how long history is kept; and the backup job: how often it runs, the hours it runs between, and whether the job and its schedule are enabled.

Secondary

The source and destination folders; how many minutes copied files are kept; the restore mode, whether readers are disconnected, the restore delay, whether every waiting file is restored, and the block size, buffer count and max transfer size; the restore alert threshold, alert number, alert on or off and history; and the copy job and restore job, each with its frequency, hours and enabled flags.

Frequency is set as a number and a unit (seconds, minutes or hours), or once a day.

The dialog shows the script it will run, and updates it as a value changes. Only what you changed is in it, so changing the restore mode is one statement naming one setting. Nothing is sent until you press Apply changes, and Copy script puts the same script on the clipboard so it can be reviewed or run elsewhere. A value SQL Server would refuse, such as an empty folder or a job set to end before it starts, is reported in the dialog instead of a script.

A chain’s settings live on different instances: the backup settings and the backup job on the primary, the copy and restore settings and their two jobs on the secondary, and the thresholds and alerts on the monitor. The dialog changes only the sides on the instance you are connected to, and says which those are. To change the other end, connect to that instance. When the monitor is another server, the threshold, alert and history fields are left out and the dialog says so. A monitor server on its own holds none of these settings, so the items are not offered there.

Changing what a chain is made of, meaning its databases, its server names and the monitor’s login, is not possible without removing and adding log shipping again, so those are not offered. Changing a folder does not move the files already in the old one.

Requirements: the login needs to be a member of the sysadmin role, which is what the sp_change_log_shipping_* procedures and the Agent schedule procedures require. A refusal from SQL Server is shown as it was said. The statements are run one at a time; if one fails the dialog reports how many had already taken effect and shows the settings as the server now holds them.

Group	Buttons
View	`Relay` · `Threshold`
	`Refresh`

There is no auto-refresh, deliberately. Restore lag changes on the copy and restore job schedule – minutes rather than seconds – so a page that requeried msdb on a timer would cost more than it told anyone. Refresh is a button.

The chosen view is remembered between sessions.

Requirements

This is a live report reading log shipping metadata from msdb on the instance you are connected to.

Which stages are visible depends on which sides of the chain this connection can read. The report records that and says so, rather than presenting a gap as a healthy zero.

The underlying query reads the _utc columns rather than the local ones, which is what makes the arithmetic correct across time zones and across a daylight-saving boundary.

It also reads restore_delay, so a deliberately delayed standby is not accused of being broken.

How to read the report

Switch to Threshold first if you have more than a couple of chains. It ranks them by how late they are relative to their own promise.
Read the band before the number. Breach means a commitment is no longer true; Within means late but agreed.
Find the gap in the relay row. The stage with the gap in front of it is the one that stopped.
Check Restore Mode and the Delayed band before treating lag as a fault.
Treat Unknown as a question, not an answer. It means the report could not see, not that there is nothing to see.
Read the Status column – it names the agent being blamed and whether that came from the agent’s own error or from the timestamps.

Common patterns

Every chain behind by the same amount. Something shared – the copy job schedule, the network to the secondary, or the secondary itself.

One chain broken, the rest fine. That chain’s restore agent, or a full backup taken out of band that broke the log chain.

Backup timestamp old on every chain. The problem is on the primary, not the secondary. Nothing downstream can fix it.

Delayed band on a standby. Working as configured. restore_delay is set on purpose.

Never restored, on a new chain. Initialisation has not completed. Worth confirming rather than assuming.

Unknown across a whole column. This connection cannot see that side of the chain. Connect to the other instance to fill it in.

Report	Why you would go there
Backup Status	Whether the backups feeding this relay are actually running.
Failed Jobs	The copy and restore agents are Agent jobs; failures show there.
Disk Space	A stalled copy stage is very often the secondary running out of room.
Files for Database	The secondary’s files, when a restore fails for space.

Frequently asked questions

Which agent do I look at first? Whichever stage the gap sits in front of in the relay chart. The Status column names it too.

Why does the report say a chain is delayed rather than broken? Because restore_delay is configured on it. It is behind on purpose, and that is not a fault.

Why are some columns Unknown? This connection cannot read that side of the chain. That is a different fact from the stage being current, so the report distinguishes them.

Why is there no auto-refresh? Because lag moves on job schedules measured in minutes. Polling msdb on a timer would cost more than it would tell you.

Why does a chain with no restores sort to the top? It has no lag, but it has never worked. That belongs with the worst rows, not the blank ones.

What is the Allowance column? The threshold configured for that leg. It is what the band is measured against – and it is why the Threshold view can compare chains with different promises.