Log Shipping
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_datewas 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 grey and stayed light grey in dark mode, and six error messages still named the Email Alerting report the page had been copy-pasted from.
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
| Column | What it is |
|---|---|
| Chain | The shipped database. |
| Role | Whether this instance is the primary or the secondary for that chain. |
| Secondary Server | The other end. |
| 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. |
| Behind By | The lag, computed. |
| Allowance | The threshold this leg was configured with. |
| Restore Mode | Standby or norecovery. |
| Status | The verdict, including which agent is being blamed and why. |
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.
The toolbar
| 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.
Related reports
| 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.