master Footprint
Overview
A master that setup created and nobody has touched is a few megabytes.
A master holding a hundred megabytes is holding somebody’s data, and the page that says whose is master User Objects next door.
This one answers the question before that: how much is here, what is it spread across, and what are the properties of this database that a reader is entitled to assume and should check anyway.
Where to find it
Expand a server in the tree, expand the master database, then Master → Footprint.
Two things that are fixed and are still worth printing
master is always SIMPLE recovery and cannot be changed
Which means it has no log chain and no log backup, so every panel about log backups that the rest of the product draws is absent here rather than empty.
master defines the server collation
The value in the grid is not master’s choice, it is the instance’s, and every other database that disagrees with it is doing so deliberately or by accident.
Version gates rather than probes
sys.dm_db_log_info arrived in SQL Server 2016 SP2 and sys.dm_db_log_space_usage in 2012.
Naming either on an older build fails the whole batch at compile time rather than the one statement, which is why the query is assembled from the version before it is sent instead of written out once with a TRY around it.
On an older instance the VLF count and log space rows are absent, with a note saying why.
Reading the chart
Two bars, one per file. Each is split into what is used, what is free inside the file, and what the file can still grow into.
master keeping its data and its log on one volume is how setup leaves it, so the header line states it rather than reporting it as a finding the way the File Map page would for a user database.
Reading the grid
| Column | What it is |
|---|---|
| Fact | The property. |
| Value | What this instance has. |
| What it means here | One sentence, written for master specifically rather than for a database in general. |
The facts include the data and log file sizes and paths, the used and free space inside them, the recovery model, the server collation, the compatibility level, the VLF count and log space (where the build supports it), the last backup, and the count of objects setup did not create.
The toolbar
| Control | What it does |
|---|---|
| User objects | Opens master User Objects, which is what a large master usually is. |
| Backup and rebuild | Opens master Backup and Rebuild Readiness. |
| Every file on the instance | Opens master File Map. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.master_files |
master’s own files, their sizes and paths. |
sys.databases |
Recovery model, collation, compatibility level. |
sys.dm_db_log_space_usage |
Log space used, on SQL Server 2012 and newer. |
sys.dm_db_log_info |
VLF count, on SQL Server 2016 SP2 and newer. |
sys.objects in master |
The count of objects with is_ms_shipped = 0. |
msdb.dbo.backupset |
The last backup of master. |
Related reports
| Report | Why you would go there |
|---|---|
| master User Objects | Whose data a large master is holding. |
| master File Map | Every file on the instance, not only master’s. |
| master Backup and Rebuild Readiness | Whether what is here is protected. |
| Failover Compatibility | Which databases disagree with the server collation. |
| Disk Space | What is left on the volume master lives on. |
Frequently asked questions
How big should master be? A few megabytes on an untouched instance. Anything much larger is user objects, and master User Objects names them.
Can I set master to FULL recovery? No. master is always SIMPLE and the engine does not allow it to change. That is why no log backup panel appears on this page.
Why is the server collation on a page about master? Because master is where it is defined. The value shown is the instance’s collation, and every other database that differs from it does so by choice or by accident.
The VLF count is missing. sys.dm_db_log_info needs SQL Server 2016 SP2. On an older build the row is absent with a note rather than blank.