DBA Scripts: Get Database Summary

Peter Whyte

–

Part of the DBA-Tools Project.

One Row Per Database, Every Issue Flagged in One Column

Checking every database on an instance one at a time, state here, recovery model there, backup age in a third window, doesn’t scale, and it’s exactly the kind of routine check that gets skipped when things are busy. The gaps that matter (a database nobody’s backed up, a log backup that quietly stopped running, auto-shrink left on from a migration years ago) hide in that skipped check.

Get-DatabaseSummary answers the “is everything actually fine” question in one query: every database on the instance, one row each, with a notes column that aggregates severity-prefixed issues (CRIT:, WARN:, INFO:) so you can scan for problems instead of reading every column by eye.

Why Database Summary Matters

This is the script for the daily or weekly “is anything quietly wrong” pass, distinct from a deep-dive into any one area:

  • The notes column does the triage for you. CRIT:not-online, WARN:never-backed-up, WARN:auto_shrink, WARN:log-backup-overdue, and an INFO:log-wait=... flag for any database stuck on an unexpected log reuse wait. A clean database has a blank notes column; anything else is worth ten seconds of your attention.
  • Backup currency is checked per database, not assumed sitewide. days_since_full and the log-backup-overdue flag catch the one database that fell out of the maintenance plan without anyone noticing.
  • auto_shrink and auto_close are two of the most common inherited misconfigurations on an older server. Both are cheap to flag here and expensive to leave running unnoticed.
  • File sizes are included so a database that’s grown unexpectedly shows up in the same pass as a database that’s stopped taking backups, rather than needing a second script.

When to Run This Script

  • As a standing daily or weekly check, the fastest way to confirm nothing on the instance quietly broke
  • After inheriting a server, to get an honest one-screen view of every database’s health before digging into any one of them
  • Alongside Get Database Health when you want the full-instance view rather than a single database’s detail
  • Before and after any maintenance window, to confirm the notes column looks the same on both sides

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DatabaseSummary
Category    : monitoring
Purpose     : One-row-per-database view of every database on the instance: state,
              recovery model, log reuse wait, file sizes, backup currency, and
              configuration flags. Notes column aggregates actionable issues.
              Reads from system metadata and msdb only — no per-database scan.
              For used vs free space detail run Get-DatabaseSizesAndFreeSpace.
              For file-level detail run Get-DatabaseFilesDetail.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : VIEW ANY DATABASE, SELECT on msdb.dbo.backupset
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

WITH backup_dates AS (
    SELECT
        database_name,
        MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS last_full,
        MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS last_log
    FROM msdb.dbo.backupset
    GROUP BY database_name
),
file_sizes AS (
    SELECT
        database_id,
        CAST(ROUND(SUM(CASE WHEN type = 0 THEN size * 8.0 / 1024 ELSE 0 END), 1)
             AS DECIMAL(18,1))                                    AS data_mb,
        CAST(ROUND(SUM(CASE WHEN type = 1 THEN size * 8.0 / 1024 ELSE 0 END), 1)
             AS DECIMAL(18,1))                                    AS log_mb,
        SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END)                AS data_file_count
    FROM sys.master_files
    GROUP BY database_id
)
SELECT
    d.name                                                          AS database_name,
    d.database_id,
    d.state_desc,
    d.recovery_model_desc                                           AS recovery_model,
    d.log_reuse_wait_desc                                           AS log_reuse_wait,
    d.compatibility_level                                           AS compat_level,
    SUSER_SNAME(d.owner_sid) COLLATE DATABASE_DEFAULT               AS owner,
    CAST(d.create_date AS DATE)                                     AS create_date,
    fs.data_mb,
    fs.log_mb,
    fs.data_file_count,
    CASE d.is_auto_shrink_on WHEN 1 THEN 'YES' ELSE 'no' END       AS auto_shrink,
    CASE d.is_auto_close_on  WHEN 1 THEN 'YES' ELSE 'no' END       AS auto_close,
    CASE d.is_read_only      WHEN 1 THEN 'YES' ELSE 'no' END       AS read_only,
    CAST(bd.last_full AS DATE)                                      AS last_full_backup,
    CAST(bd.last_log  AS DATE)                                      AS last_log_backup,
    DATEDIFF(DAY, bd.last_full, GETDATE())                          AS days_since_full,
    -- Severity-prefixed issue flags; NULL = clean
    NULLIF(RTRIM(
          CASE WHEN d.state_desc <> 'ONLINE'
               THEN 'CRIT:not-online ' ELSE '' END
        + CASE WHEN d.is_auto_shrink_on = 1
               THEN 'WARN:auto_shrink ' ELSE '' END
        + CASE WHEN d.is_auto_close_on = 1
               THEN 'WARN:auto_close ' ELSE '' END
        -- Backup warnings apply to user databases only (database_id > 4); tempdb excluded implicitly
        + CASE WHEN d.database_id > 4 AND bd.last_full IS NULL
               THEN 'WARN:never-backed-up ' ELSE '' END
        + CASE WHEN d.database_id > 4 AND bd.last_full IS NOT NULL
                    AND DATEDIFF(DAY, bd.last_full, GETDATE()) > 7
               THEN 'WARN:full-' + CAST(DATEDIFF(DAY, bd.last_full, GETDATE()) AS VARCHAR) + 'd-ago '
               ELSE '' END
        + CASE WHEN d.database_id > 4 AND d.recovery_model_desc = 'FULL'
                    AND (bd.last_log IS NULL OR DATEDIFF(HOUR, bd.last_log, GETDATE()) > 24)
               THEN 'WARN:log-backup-overdue ' ELSE '' END
        -- Log reuse waits other than NOTHING and LOG_BACKUP (expected) are worth noting
        + CASE WHEN d.log_reuse_wait_desc NOT IN ('NOTHING', 'LOG_BACKUP')
               THEN 'INFO:log-wait=' + d.log_reuse_wait_desc + ' ' ELSE '' END
    ), '')                                                          AS notes
FROM      sys.databases  d
LEFT JOIN file_sizes     fs ON fs.database_id   = d.database_id
LEFT JOIN backup_dates   bd ON bd.database_name = d.name
ORDER BY d.database_id;

Joins sys.databases against sys.master_files (for size) and msdb.dbo.backupset (for backup currency), then builds the notes column from a set of severity-prefixed CASE checks. Reads system metadata and msdb only, no per-database scan, so it’s cheap to run often.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# One-row-per-database health summary with an aggregated issues column:
.\run.ps1 Get-DatabaseSummary

# To run against a remote sql server:
.\run.ps1 Get-DatabaseSummary -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

Get-DatabaseSummary output showing key columns, SQL Server output

Real output captured 2026-07-31 07:16 against ..

Understanding the Results

The lab instance’s own output has a genuine finding worth walking through: DBAMonitor (the collector infrastructure database created earlier in this project) shows WARN:never-backed-up WARN:log-backup-overdue, an honest gap, not a manufactured example. DemoDatabase and the migdb_* lab databases show WARN:log-backup-overdue even though their full backups are only 3 days old, because they’re on FULL recovery with no log backup taken since. The four system databases (master, tempdb, model, msdb) all show a blank notes column by design: the backup and log-reuse-wait checks apply only to database_id > 4, so system databases never get flagged for missing backups they’re not expected to have.

Notes-column prefixes, in the order the script checks them:

  • CRIT:not-online — the database isn’t ONLINE. Everything else about it is secondary until this is understood.
  • WARN:auto_shrink / WARN:auto_close — configuration flags that hurt performance quietly, worth fixing even outside an incident.
  • WARN:never-backed-up — a user database with zero rows in msdb.dbo.backupset. The most serious of the backup warnings.
  • WARN:full-Nd-ago — the last full backup is more than 7 days old.
  • WARN:log-backup-overdue — a FULL recovery database with no log backup in the last 24 hours, or none at all.
  • INFO:log-wait=... — log_reuse_wait_desc is something other than the two expected steady states (NOTHING, LOG_BACKUP). Informational, not automatically a problem, but worth reading.

Best Practices

  • Run this on a schedule (daily is reasonable given how cheap the query is) rather than only when something’s already gone wrong.
  • Treat a blank notes column as the actual goal state for every user database, not just an absence of alarm.
  • Pair it with Get Database Health when a specific database’s notes column flags something. This script tells you which database needs attention; that one goes deeper.

Related Scripts

You may also find these scripts useful:

Summary

Get-DatabaseSummary is the script to run when you want one honest answer to “is anything on this instance quietly broken” without opening a single database properties dialog. The notes column does the triage work, so a clean run costs ten seconds of reading and a genuine finding jumps straight off the page, the way DBAMonitor‘s WARN:never-backed-up does in the real output above.

It’s not a replacement for Get Database Inventory‘s migration-focused view or Get Database Health‘s single-database depth. Run this one on a schedule, and reach for those two when it flags something worth a closer look.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *