Backup Size

Overview

Backup Size answers how big are the backups and where are they going, for one database, over the whole of the retained backup history.

A line chart with one series per backup type sits over a grid of every backup with its start, its end, its duration, its size, its compressed size and where it was written.

The chart is the report. Backup sizes are boring numbers and interesting shapes: the slope tells you when you run out of disk, the sawtooth tells you whether the differential chain is being reset, and a step change tells you something happened to the data.

The Backup Size report
One line per backup type over the retained history, with every backup listed below.

Where to find it

A database level report. Expand the database → Real Time → Backup Size.

The page title reads Backups for <database name>.

The node is not offered for tempdb, which is never backed up.

Requirements

  • No minimum version. A separate query is used on SQL Server 2005 and older.
  • Access to msdb. The backup history lives there.
  • Backup history that has not been purged. The chart can only show what msdb still holds, and most instances run a job that trims it.

The filter

A dropdown at the top right with four choices: All, Full, Differential and Log. It opens on All and remembers what you last chose.

All draws three lines at once, and it is the view worth starting from.

Series Colour
Full Blue
Differential Green
Log Teal

Picking a single type redraws the chart as one line for just that type, which is the view for reading a trend in detail once you know which type you care about.

Why All is the interesting view

The three types answer different questions and the relationship between them is where the problems show up.

  • Full backups track the size of the data. A steady climb is growth. A step is a load.
  • Differentials should reset to near nothing after each full and grow between them. A differential that never resets means the full backups it should be resetting against are not happening, or are being taken with COPY_ONLY.
  • Log backups track how much changes. A log backup an order of magnitude larger than usual is index maintenance, a large delete, or a job that ran once and should not have.

The chart scale

The Y axis is auto-scaled to bytes, KB, MB, GB or TB, chosen from the smallest backup on the chart, and the unit is written into the axis title and the chart title. This matters when you are comparing two databases by eye: check the unit before comparing the shapes.

Each point carries a tooltip with the exact date and size.

Reading the grid

Column What it is
Start Time When the backup started.
End Time When it finished.
Duration How long it took.
Backup Size The uncompressed size of the data backed up.
Compressed The size actually written.
Ratio Compressed as a percentage of uncompressed. Lower is better; 100% means no compression.
Backup Type Full Database, Differential or Log, with (COPY_ONLY) appended where that applies.
Description The description recorded with the backup, where one was given.
File Name The physical device the backup was written to.

Newest first.

The (COPY_ONLY) marker is worth looking for. A COPY_ONLY full backup does not reset the differential base, and a COPY_ONLY log backup does not truncate the log. A backup routine built out of COPY_ONLY fulls looks complete on this page and leaves the differentials growing without bound.

Double-click a row to open the Backup Advisor for that backup, which explains that backup type, what it can restore and what it needs alongside it.

The backup grid
Ratio is compressed as a percentage of the original. 100% means compression was off.

How to read the report

  1. Start on All and look at the shape, not the numbers.
  2. Check the differential line resets. A sawtooth is correct. A staircase means the full backups are not resetting the base.
  3. Read the Full line’s slope. That is the storage forecast for your backup target. Disk Space Forecast does the same job for the volumes.
  4. Look for log spikes. Then find what ran at that time in Job Schedules or Job History.
  5. Read the Ratio column. A column of 100% means backup compression is off, and turning it on is usually the cheapest win available on the whole backup routine. Configuration Values shows the backup compression default setting.
  6. Check Duration against the window. A backup whose duration has doubled over six months will eventually run into the business day.

Common patterns

Ratio at 100% on every row. Compression is off. On typical data a full backup compresses to somewhere between a fifth and a half, which cuts both the storage and, because less is written, usually the duration.

Differentials that keep growing and never reset. Something is taking COPY_ONLY fulls, or the fulls are being taken by a different tool that this history does not record. By the time a differential is bigger than the full, it has stopped being worth taking.

A step change in the Full line. A data load, an archive that never happened, or a new large table. Table Sizes and Table Space Breakdown say which.

One enormous log backup. Index maintenance, a bulk load or a mass update. Normal if it lines up with a scheduled job, worth chasing if it does not.

Log backups stop for a period and then resume with a large one. The log backup job failed for that stretch. Failed Jobs should show it, and Backup Status shows the exposure it left.

No rows at all. Either the database has never been backed up, or the backup history has been purged. Both are worth knowing, and Backup Status distinguishes them.

Where the data comes from

  • msdb.dbo.backupset for the backup, its type, its sizes and its timings.
  • msdb.dbo.backupmediafamily for the physical file it was written to.

Nothing is stored by this page. It reads msdb on the monitored instance, which means the history is only as long as your msdb retention allows.

The chart plots at most 1000 points per series. On a database with years of log backups, the chart is the most recent 1000 of each type rather than all of them.

Report Why you would go there
Backup Status Whether the backups add up to a restore, and how much data is exposed right now.
Backup Time Frames Where the backups sit on the clock over the last fourteen days.
Recovery Exposure How long a restore of this database would actually take.
Disk Space Forecast When the volume holding the backups runs out.
Job Schedules and Job History Which job takes each of these backups, and when.
Configuration Values The backup compression default setting behind the Ratio column.

Frequently asked questions

Why does the page have no rows? The database has never been backed up, or the msdb history has been purged. Backup Status will say which.

What does the Ratio column mean? Compressed size as a percentage of the uncompressed size. 100% means no compression; 25% means the backup was written at a quarter of the size.

Why is the differential larger than the full? Because the differential base has not been reset. That usually means the full backups are COPY_ONLY, or are being taken by a tool whose backups are not in this history.

What does (COPY_ONLY) mean on a row? The backup was taken without disturbing the backup chain. It does not reset the differential base and a copy-only log backup does not truncate the log.

Why does the chart show fewer points than the grid? The chart caps at 1000 points per series. The grid is not capped.

How is this different from Backup Status? This is the size and duration history. Backup Status is whether you could restore, and how much you would lose.

Why is Backup Size missing on tempdb? Because tempdb is recreated at every startup and is never backed up.