Databases by Size
Overview
Every database on the instance ranked by the space it has been allocated, with the split between data and transaction log carried in the chart rather than left in two grid columns for you to compare by eye.
The question this page exists for is not “which database is biggest”, which a sorted grid answers perfectly well. It is “which database is mostly log”, and that is a comparison between two numbers on one row, repeated for every row, which nobody does.
What this replaces
The old page was a generic treemap over a ten column grid. It shaded tiles by a heat ramp bucketed on total divided by sixteen, so on an instance with the usual power law size spread everything outside the top two or three came back the same blue. In dark mode the whole ramp was six greys inside twenty RGB units.
Colour was being spent restating what the area of a tile already said. The chart also quietly stopped after fifty databases while the query fetched a thousand, and nothing on the page gave a total, a share, or any reason to look at one row before another.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Reports → Databases by Size |
The page title reads Databases By Size for <server name>.
Requirements
- Permission to read
sys.master_files. On a login that cannot, the page says so rather than drawing an empty instance. - Nothing is installed and nothing is written.
There is no Real Time and Historic pair, and no restart notice. File sizes come from sys.master_files, which is catalog metadata rather than a usage counter, so there is no collection window to caveat.
Reading the treemap
Tile area is that database’s share of the total allocated space on the instance. Tiles are squarified, so they stay close to square and remain readable rather than degrading into slivers.
Colour is spent on one thing only: how much of a database is transaction log. Two thresholds, both stated in the legend and the tooltips rather than left to be reverse engineered:
| Band | When | What it means |
|---|---|---|
| Normal | Log under a quarter of the database | Nothing to see. |
| Log Heavy | Log is a quarter or more | Worth knowing. Often a large index maintenance window or a recovery model nobody revisited. |
| Log Over Data | Log is larger than the data | Almost always a log that cannot truncate: no log backups in full recovery, a long open transaction, or replication holding it. |
That is the escalation the old heat ramp had no room for, because it was already using colour to restate size.
Ranked view
The same model drawn as a ranked list instead of tiles, for instances with too many databases to draw as tiles at a readable size. The toolbar switches between them.
Reading the grid
The grid carries the totals per database: allocated, data, log, and on instances that have them, FILESTREAM and full text index space, plus the recovery model and state.
The size columns sort correctly. The old grid stored the formatted string, and the comparer reads the leading number out of a cell, so 2 GB sorted below 900 MB. The rebuilt grid keeps the raw byte count behind the formatted text.
There is a headline totalling the whole instance, which the old page did not have. Without it a tile’s area has nothing to be a share of.
How to read the report
- Read the headline total first. Everything on the page is a share of it.
- Look for amber and red tiles before big ones. A big database is usually just a big database. A database that is mostly log is a problem with a cause.
- For anything Log Over Data, check the recovery model. Full recovery with no log backups is the most common single cause.
- Switch to Ranked if there are too many databases for the tiles to be readable.
- Go to VLFs for any database whose log is large, to see whether it also has a bad internal structure.
Common patterns
One enormous tile and a scatter of small ones. Normal. This is what nearly every instance looks like.
A small database drawn red. Log over data on something small is still worth a look, and it is usually the fastest fix on the page.
Several databases Log Heavy at once. Usually one cause across the instance: a log backup job that stopped, or a maintenance window that ran long.
A database with almost no data and a large log. Often a database that was emptied without the log being shrunk afterwards, or one where a rollback is still running.
Where the data comes from
sys.master_files for file sizes, which is instance-wide catalog metadata and needs no connection to each database.
There is a fallback pass: any database that reported no file sizes is opened individually. On a server with a great many databases that is the slow part of the report, and it is why the read is not instant on large instances.
Nothing is stored. For size over time, use File Size Over Time or Disk Space Forecast.
Messages you may see
Nothing readable:
No database on this instance reports any allocated space. That usually means the login cannot read sys.master_files or the databases in question.
Timed out:
The database size scan did not finish in time. A server with a great many databases can take a while to read; press F5 to try again.
Related reports
| Report | Why you would go there |
|---|---|
| VLFs | Whether a large log is also badly structured internally. |
| Disk Space | Whether the volume underneath has room for any of this. |
| Disk Space Forecast | Where this is heading. |
| File Utilization | Allocated against actually used, per file. |
| Table Space Breakdown | Where the space inside one database has gone. |
Frequently asked questions
Is this allocated space or used space? Allocated. It is what the files occupy on disk, not how full they are. File Utilization answers the other question.
Why is colour not showing me size? Because the area of a tile already does. Colour is spent on the log profile, which nothing else on the page can show.
What counts as Log Heavy? Log at a quarter of the database or more. Log Over Data is log larger than data. Both thresholds are in the legend.
Why is there no history? File sizes come from catalog metadata rather than a counter, so there is no collection window here. Use File Size Over Time for the trend.
Why did the old chart stop at fifty databases? It did, and the query fetched a thousand. That is one of the reasons this page was rebuilt.