Database Use

Overview

The Database Use report places every database on the instance by how recently anything read it, against the space it holds – so the big database nobody queries stands out.

It is the instance level counterpart to the database level Table Use report, and it is the same finding one level up: the intersection of idle and expensive. Either half on its own is much weaker. A list of databases nobody reads says nothing about whether they matter, and a list of large databases says nothing about whether they are needed.

A recency band across the top puts every database into one of five groups, from read-today through never-read, and clicking a group filters the page to it. Below that, one bar per database, colored by recency.

Why this report exists

Three instance reports already ranked databases, and all three rank the busy ones:

Report Ranks databases by
CPU by Database cumulative plan cache CPU
I/O by Database bytes moved and time waited
Databases by Size allocated space

Nothing ranked the idle ones. This report leads with recency rather than volume, so it answers the question none of the others do: which databases on this server is nobody using, and what are they costing in space. The activity counts are supporting detail, not the headline.

Where to find it

An instance level report. Right-click the server → Instance Reports → Database Use.

What the page will not claim

Four things about this data would make the report lie if they were not stated, so all four are on the screen rather than buried in this page.

The restart caveat

Read counts come from sys.dm_db_index_usage_stats, which is emptied by a service restart. The subtitle always says when the counters started, and a notice band appears while they are still too young to judge. A database in the never-read group on an instance that restarted this morning has not been proved unused – it has only been unread since breakfast.

AUTO_CLOSE discards the counters

For a database the counters also reset when it goes OFFLINE, is detached, or auto closes. An AUTO_CLOSE database can therefore read as never-used when it is nothing of the sort. Auto close is detected, chipped, and never accused – the tooltip says the counters may have been thrown away rather than that nothing read the database.

A database that cannot be read is reported, not accused

Offline, restoring, recovering and read-only databases cannot show reads. Those get a stated status chip instead of the amber verdict.

System databases are hidden by default

master, model, msdb, tempdb and the like are always busy and drown the finding, so the page opens on user databases. The footer says how many are hidden rather than filtering silently, and All includes them.

The Database Use report
The whole report. The recency band across the top filters the page when clicked.
Bars sized by allocated space, colored by recency
Space and recency together – the big database nobody reads is the one that stands out.

Reading the grid

The database use grid
Reads next to Writes is worth reading as a pair.
Column What it is
Database The database name.
Size Total allocated space, data plus log.
Reads Seeks, scans and lookups since the counters began.
Writes Updates since the counters began.
Sessions Sessions connected right now.
Last Read When anything last read it.
Days Idle How long since that read. A + means at least, not exactly.
Group The recency band.
State Online, Offline, Restoring, and so on.

Reads next to Writes is worth reading as a pair. A database with millions of writes and no reads at all is still being fed and is feeding nothing, which is a very different conversation from one that is simply quiet.

Sessions separates nobody has queried it from nothing is even attached to it.

The chips

Chip Meaning
Active Read today.
Idle N days Not read for at least thirty days.
Not read since restart No read at all in the counter window.
Written, never read Still being written to, never read. The strongest abandoned-but-still-fed signal.
Auto close Counters unreliable – closing the database discards them.
Read only · Offline · Restoring Context for the silence, not a verdict.
Created N days ago Too new to judge.

The toolbar

Group Buttons
Scope User databases · All
Rank by By last use · By activity · By size
Refresh · Pause

The three rankings are three different axes, and they disagree often enough that flipping between them is the point – the database at the top of one is regularly near the bottom of another. By last use is the default.

  • By last use – recency. Most recently read first. Bar stays on space, because recency has no length of its own, so this reorders the bars rather than rescaling them.
  • By activity – frequency. Most operations first. Bar becomes reads plus writes.
  • By size – space. Largest first. Bar is allocated space.

The rank toggle re-sorts without re-querying.

Finding the never-read databases. None of the three rankings puts them first – a never-read database sorts to the bottom of By last use. Click the Never segment of the recency band to filter the page to them. That is the intended route to the finding.

The scope toggle does not re-query either. Every database comes back in the one query and the filter is applied in the report.

Cost

One query, no per-database pass. Everything the page reads is server scoped – sys.dm_db_index_usage_stats carries a database_id column, and sys.master_files, sys.databases and sys.dm_exec_sessions are instance wide – so unlike Large Tables this report never opens a database of its own. That is what makes it cheap enough to auto refresh.

It needs VIEW SERVER STATE on the instance.

The drilldown

Double-click any database to open that database’s own Table Use page. That is the point of the pair:

This page finds the idle database. That page finds the idle tables inside it.

Right-click offers investigation scripts only – the drilldown, the database name, and the per-table usage rollup for inside the database. There is no detach and no drop. An unused database still holds data, so this page points rather than shoots.

How to read the report

  1. Check when the counters started. The subtitle says. Everything below depends on it.
  2. Click the never-read band to filter to the candidates. The page opens ranked by recency with the most recently read first, so the never-read group is at the far end of the list until you filter to it.
  3. Rank by size. Big and unread is where the space is.
  4. Check the chips. Auto close, offline and recently created all explain a silence the counters cannot account for.
  5. Check Writes. Written but never read is a live ETL target nobody consumes, not a dead database.
  6. Drill in. Double-click to see whether the whole database is idle or only parts of it.
  7. Confirm before acting. Read counters do not see a database read only by a quarterly job.

Common patterns

A large database in the never-read group, created years ago. The classic finding – a migration leftover or a retired application nobody removed.

Millions of writes, zero reads. Something is still loading it. Find the job before you touch the database: the writer will fail, and it may be the only thing that notices.

Every database looks unread. Check the notice band. The instance almost certainly restarted recently.

A quiet database with sessions connected. Something is attached and idle – often a pooled application connection against a database the application no longer really uses.