msdb Space and Retention

Overview

msdb is the one system database that is mostly somebody’s data rather than the engine’s. Backup and restore history, every Agent job and every run of one, mail items and the log of what happened to them, maintenance plans, packages, policies: all of it accumulates for years, and none of it is trimmed unless somebody trims it.

This is the page the rest of the msdb suite is measured against. Every other page in the suite counts something inside a window nobody chose deliberately:

  • backup history that goes back to the day the instance was built
  • job history bounded by a row limiter somebody turned off in 2019
  • mail items from a profile that stopped working two years ago

A count with no window on it invites the reader to compare it against an instance with a different one. This page supplies the window.

The msdb Space and Retention page: what is holding the space and how far back it goes
The whole page in the What holds the space view. msdb is 195MB with 128MB of that table data, and backupset alone holds 27.5 percent of it.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Space and Retention.

The msdb group is shown for the database named msdb and for no other. Unlike the report server and Integration Services catalogs next door, this needs no probe: a database called msdb on a SQL Server instance is msdb, it cannot be renamed, and the one edition where that would not hold (Azure SQL Database) exposes no msdb at all.

Requirements

Requirement Why
VIEW DATABASE STATE on msdb sys.dm_db_partition_stats is where the space figures come from. This is the only permission the page needs, and it is not one of the Agent roles.

A login without it gets a message rather than an empty chart. See Messages you may see below.

The two views

View What each bar is
What holds the space One bar per table, split into the data, the indexes and the free space inside the pages the table has already taken.
How far back it goes One bar per history source, measured in days of history rather than in rows, with the procedure that trims it named.

Switching views does not go back to the server. Both come from one read.

Why the space split matters. The three parts have three different answers:

Part What it means
Rows on disk The data itself. The answer is a purge.
Indexes The answer is still a purge, and the purge will be slower for it.
Free inside Space inside pages the table already holds and is not using. A purge releases rows into this; only a rebuild gives it back to the file.

Why retention is measured in days. Rows are what the space view already showed. Days are what somebody decides. “Two years of backup history” is a retention policy; “eleven million rows” is a symptom.

Tables holding less than 64 KB are left off the chart. msdb has around two hundred tables and all but a dozen of them are empty catalogs holding one page each.

Reading the chart

The bars: reserved space per table, split into rows, indexes and free space
The four backup tables at the top are one source rather than four findings, which is what the Trimmed by column in the grid names.

One bar per table or per history source, ranked, longest first, scaled to the largest value on the chart.

  • Segments within a bar are one hue in lightness steps, not separate hues, because they are parts of one quantity rather than categories. That keeps the comparison readable without a substitute palette in color blind mode.
  • Red always means broken, never “the fourth item”.

Click a bar to select its grid row. Right-click for Copy Chart to Clipboard.

Reading the grid

The grid: rows, space, the span of dates, and what trims each table
Oldest row and Newest row read together are the shape: an old oldest with a recent newest is a source nobody purges. Trimmed by names the procedure that would do it.
Column What it is
Table The table, schema qualified when the schema is not dbo.
Rows Row count from the partition statistics.
Reserved Everything the table has taken from the file.
Rows on disk The data pages.
Indexes Reserved minus used, which is what the non-clustered indexes hold.
Free inside Reserved minus used: allocated and not occupied.
Share of msdb That reserved figure as a share of the whole database.
Oldest row The oldest timestamp this table holds. Amber at a year or more.
Newest row The newest, which says whether the source is still writing.
Trimmed by The procedure that removes rows from it, or not installed by SQL Server for a table setup did not create.

A table with an old “Oldest row” and a recent “Newest row” is the ordinary shape of a source that nobody purges. A table with an old date in both columns is a feature that was switched off and left its history behind.

The caveat line

Under the chart summary, in the same words on every msdb page:

  • msdb’s recovery model, when it is not the default
  • whether msdb has been backed up, and how long ago
  • how many tables in this msdb were not installed by SQL Server setup, because a rebuild of msdb would lose them (see Third Party Objects)
  • the read scope, which is the clause that decides whether the numbers mean what they appear to

Nothing here runs anything

A single sp_delete_backuphistory on a neglected instance can run for hours, holding locks on the tables Agent writes to at the end of every job. That is a decision somebody makes with a maintenance window open, not one made behind a click on a report.

So the purge statements are offered as text to copy and never executed. The Trimmed by column names the procedure for each source, and the right-click menu copies the statement for the selected row.

Where the data comes from

Source What it gives
sys.dm_db_partition_stats Reserved, used and data page counts per table.
sys.tables, sys.schemas The object names, and is_ms_shipped for the third party test.
dbo.backupset, dbo.restorehistory Oldest and newest backup and restore records.
dbo.sysjobhistory Oldest and newest job history.
dbo.sysmail_mailitems, dbo.sysmail_log Oldest and newest mail.
dbo.sysmaintplan_logdetail Oldest and newest maintenance plan log.
dbo.backupfile, dbo.backupmediafamily, dbo.backupmediaset The rest of the backup ledger’s weight.
sys.databases, dbo.backupset msdb’s own recovery model and last full backup, for the caveat line.

The read has a 180 second timeout. On an instance where msdb has been left alone for a decade, the partition statistics read is the slow part.

Messages you may see

Nothing in msdb could be measured. sys.dm_db_partition_stats answered with no rows, which needs VIEW DATABASE STATE on msdb rather than any of the Agent roles.

The query did not come back. On msdb this is a real risk rather than a formality. Backup history and job history are the two tables in the product most likely to hold tens of millions of rows.

Report Why you would go there
Database Mail History Where the weight in sysmail_mailitems and sysmail_attachments came from.
Restore History The other half of the backup ledger, deleted by the same procedure.
Backup Ledger Which databases the backup history rows belong to, and whether a purge would finish.
Agent Settings The job history row limiter, which is what decides how much sysjobhistory keeps.
Third Party Objects What is in msdb that SQL Server setup did not put there.
Maintenance Plans sysmaintplan_logdetail is trimmed by nothing but sp_maintplan_delete_log.

Frequently asked questions

Why is msdb forty gigabytes? Almost always backup history, and almost always because a third party backup tool writes one backupset row per file per database per interval. The Backup Ledger page has the rows per day figure that shows it.

I purged and the file did not shrink. A purge moves space from Rows on disk to Free inside. It is released inside the pages the table already holds. Only a rebuild of those tables returns it to the file, and only a shrink returns it to the disk.

Why is a table listed with “not installed by SQL Server” in the Trimmed by column? Because nothing in SQL Server trims it. It came from a backup tool, a monitoring agent or a maintenance script, and whatever installed it is what has to trim it.

The days of history and the row count disagree about which source is worst. That is the point of having both views. Two years of tiny mail log rows is a retention problem; three weeks of a third party tool’s backup rows can be most of the database.