Backup Ledger
Overview
Backup Status answers whether the backups are current. This page is about the five tables that answer is written on, which nobody looks at until the day they have to:
backupset, backupmediafamily, backupmediaset, backupfile and backupfilegroup, plus the restore history that is deleted along with them.
Where to find it
Expand a server in the tree, expand the msdb database, then MSDB → Backup Ledger.
Four things it can say that no other page can
Which databases have history here and are no longer on this instance
Every one of those is somebody’s decommissioned database, a failover partner’s copy, or a restore test from 2021. They are usually most of the rows.
Which databases are on this instance and have no history at all
Either a database that has never been backed up, or a purge that went further than somebody meant.
Where the rows are coming from
A third party tool that writes one backupset row per file per database per fifteen minutes is the usual reason msdb is forty gigabytes. The rows per day figure is what shows it, and it is the figure nobody has because nobody divides.
Whether a purge would finish
sp_delete_backuphistory walks these tables by media_set_id and backup_set_id. On an instance where those indexes are missing it runs for hours, holding locks on the tables Agent writes to at the end of every job.
The indexes are checked rather than assumed. SQL Server has shipped most of them since 2016, and a page that told a modern instance to add an index it already has would be worse than saying nothing.
The two views
| View | What each bar is |
|---|---|
| By history rows | Databases ranked by how many rows of backup history they hold. This is the view for the purge question. |
| By bytes backed up | Databases ranked by the volume that history describes. This is the view for the storage question. |
They disagree, and the disagreement is informative: a database with a million rows and very few bytes is being backed up far more often than anybody intended.
Reading the chart
One bar per database name, with the history row count on the right. By bytes backed up ranks the same databases by the volume that history describes instead.
The two views disagreeing is the finding: a database near the top here and near the bottom there is being backed up far more often than anybody intended.
The bars draw the top thirteen of the twenty. The grid holds them all.
Reading the grid
| Column | What it is |
|---|---|
| Database | The database the history belongs to. |
| State | On this instance, or gone (history with no database), or no history (database with no rows). |
| History rows | Rows in backupset for this database. |
| Fulls / Diffs / Logs | The row counts by backup type. |
| Backed up | Total bytes these backups describe. |
| Oldest / Newest | The span of the history. |
| Verdict | What this row means: a decommissioned database still holding rows, a database nobody backs up, or a rate that is filling msdb. |
The toolbar
| Control | What it does |
|---|---|
| By history rows / By bytes backed up | The two views. |
| Backup status | Opens the Backup Status report, which answers whether the backups are current. |
| Restore history | Opens Restore History, which the same purge deletes. |
| Space and retention | Opens msdb Space and Retention. |
Where the data comes from
| Source | What it gives |
|---|---|
dbo.backupset |
One row per backup: the database, the type, the size, the start and finish. |
dbo.backupmediaset, dbo.backupmediafamily |
The media the backup was written to, and how many files it was split across. |
dbo.backupfile, dbo.backupfilegroup |
One row per database file per backup, which is where the row count multiplies. |
dbo.restorehistory |
The restores, counted here because the same purge removes them. |
sys.databases |
Which databases exist now, for the gone and no-history comparison. |
sys.indexes on the five tables |
Whether the indexes sp_delete_backuphistory needs are present. |
Messages you may see
There is no backup history and no database to have any.
backupsetis empty andsys.databasesreturned nothing to compare it against, which is not a state a working instance reaches.
Related reports
| Report | Why you would go there |
|---|---|
| Backup Status | Whether the backups on this instance are current. |
| Backup Speed | How fast each of these backups runs, and whether it is getting worse. |
| Growth from Backups | The size history hiding in the same table. |
| Restore History | What has been put back, and from where. |
| msdb Space and Retention | What these five tables weigh, and what trims them. |
Frequently asked questions
Most of my backup history is for databases that do not exist. That is the usual finding, and it is the cheapest purge available: those rows describe backups of databases nobody will restore.
How many rows per day is too many? It depends on the instance, which is why the page gives the figure rather than a threshold. One row per database per backup is normal. One row per file per database per quarter hour is a third party tool with log backups on a short schedule, and it adds up fast.
Can I just delete from backupset directly? You can, and the foreign keys between the five tables will make you do it in the right order or not at all. sp_delete_backuphistory exists to get that order right, which is why the page names it rather than offering a delete.
What if the indexes are missing? The page says which ones, and the statements to create them are on the clipboard from the right-click menu. Creating them before the purge is usually much faster than running the purge without them.