Database Overview
Overview
The Database Overview is what a database opens on, the same way the Server Overview is what an instance opens on. It is a stack of panels, and between them they answer four questions about one database:
- How much space is it using, and where? Data and log allocated against used, and how many virtual log files the log has been cut into.
- Is it being backed up? Recovery model, what ran in the last 24 hours, and when the last full and log backups actually finished.
- What is happening in it right now? Blocking, active queries and buffer cache hit ratio, all live.
- What should somebody know about it? A Warnings and Errors list, which is the part of this page most worth reading.
The page title reads Real Time Overview: <database name>, which is the name it has had for a long time and the name F1 still looks its help up under.
Where to find it
| Route | How |
|---|---|
| Server tree | Expand an instance and click a database name |
| Back button, or Backspace | From any report scoped to that database |
Every database level report hangs off this page’s node in the tree, under Historic, Real Time and Indexing.
Reading the page
Disk usage
| Reading | What it says |
|---|---|
| Data allocated / used | The size of the data files, and how much of that is not free space |
| Log allocated / used | The same for the log |
| VLF count | How many virtual log files the transaction log has been divided into |
Allocated and used are given together on purpose. A data file that is 400 GB allocated and 90 GB used is not a 400 GB database, and the difference is the number that decides whether there is a space problem to solve.
The VLF count is only shown when it can be read. It comes from DBCC LOGINFO, which needs sysadmin, so on a login without it the reading is left off the panel rather than shown as zero.
Four warnings can appear in this panel, and each one is a link:
| Warning | When |
|---|---|
| Warning Data Files on C Drive | Any data file lives on C: |
| Warning Log Files on C Drive | Any log file lives on C: |
| Danger Huge Log Files | The log is more than twice the data size, and over roughly 300 MB |
| High VLF Count | More than 250 virtual log files |
The huge log warning escalates rather than repeating itself. Past about 1 GB the log figure turns red and reads DANGER DANGER; past about 3 GB it reads DANGER DANGER DANGER. It is the same finding in all three cases, said louder.
DBAssistant
The AI panel, in the same place on this page that it holds on the Server Overview. It asks about this database rather than about the instance.
Backups
| Reading | What it says |
|---|---|
| Recovery Model | FULL, BULK_LOGGED or SIMPLE |
| Full backups in the last 24 hours | A count, and when the last one finished |
| Log backups in the last 24 hours | A count, and when the last one finished |
| Differential backups in the last 24 hours | A count |
With no full backup in the last 24 hours the count is replaced by a link reading No Full Backups in 24 hours, and the last full backup date is still given underneath, so the panel says how far back you have to go rather than only that today is missing.
tempdb is a special case. It carries a single line saying there is no need to back tempdb up, and it is not allowed anyway, rather than a panel of zeroes and a warning that would be wrong.
For the full history rather than the last day, open Backup Size or Backup Time Frames, and Recovery Exposure for what an outage would actually cost.
Blocking, active queries and cache hit ratio
Three live panels, each refreshing on its own background reading.
| Panel | What it shows | Click opens |
|---|---|---|
| Blocking Queries | How much blocking there is in this database | Blocking Tree |
| Active Queries | What is running now | Active Queries for this database |
| Buffer Cache Hit Ratio | How often a page was found in memory | Nothing, it is a reading |
Blocking drills through to the instance level Blocking Tree rather than to a database page. Blocking chains cross databases, so a chain that starts here usually cannot be explained from inside here.
Warnings and Errors
A two column list: the warning, and a link with more detail. Double-click a row to open the link in a browser.
The list is built as the checks finish rather than all at once, so a warning can appear a second or two after the page has drawn.
| Warning | What it means |
|---|---|
| Compatibility level below 90 | Some reports will not run against the database at all |
| Log file unusually large compared to the data file | The log has grown and has not been reclaimed, usually a missing log backup |
| Log or data files on the C drive | Filling C: takes the whole server down, not just this database |
| DB Collation differs from TempDB Collation | Every temp table join risks a collation conflict |
| Obsolete Page Verify Option | The database is on TORN_PAGE_DETECTION or NONE rather than CHECKSUM |
| Database Set to AutoShrink | Shrink and regrow in a loop, fragmenting everything as it goes |
| Veeam (or other third party) backup breaking the restore chain | Full backups taken outside SQL Server alongside log backups taken inside it |
The last one is worth reading twice. It fires when the database has both third party full backups in the last week and its own log backups, which is the combination that produces a log chain nobody can actually restore from.
SSRS
A database holding a report server catalog gets one more panel, above the rest: the SSRS Overview, with the three SSRS reports behind it. An ordinary database gets nothing here, and the check that decides costs one OBJECT_ID call that is cached for the session.
Refresh
The live panels share one background reading, on the interval set by DatabaseRefreshInterval in settings, which defaults to five seconds.
The rest of the page is read once when it opens. File sizes, recovery model, backup history and every check behind the warnings list answer slow moving questions, and re-asking them every five seconds would put real queries against msdb and sys.database_files on a loop for readings that change once a day. Press F5 to read them again.
Messages you may see
No instance selected, in the debug log rather than on the page, when the page is reached without a connection behind it. The three live panels are left empty.
There is no need to have backups on TempDB, on the backups panel of tempdb.
Database Compatibility level of N is less than 90, in the warnings list, when reports that need 90 or higher will refuse to run.
Related reports
| Report | Why you would go there |
|---|---|
| Files | Every file, its size, its growth setting and where it lives |
| VLFs | The virtual log file count in detail, and what to do about it |
| Backup Size | Backup history over time rather than the last 24 hours |
| Recovery Exposure | What an outage right now would cost |
| Indexing Overview | The indexing health of this database, with a card per report |
| Technical Debt | What has accumulated in this database over the years |
| Table Sizes | Where the space in the data file has gone |
Frequently asked questions
Why is there no VLF count? DBCC LOGINFO needs sysadmin. Without it the reading is left off rather than shown as zero.
The warnings list is empty. Is that good? Yes. It is only built when there is something to put in it.
Why does the blocking panel open an instance level report? Because blocking chains cross databases. A blocked session in this database is very often waiting on a session working in another one.
Why do the backup numbers not refresh? They are read once when the page opens. Backup history is in msdb and changes a few times a day. F5 re-reads the page.
Why does the page say Real Time Overview and the tree say the database name? The page title has read Real Time Overview since long before this page had panels on it, and the help system, the back history and the tab strip are all keyed on it.