DBA Scripts: Get Database Summary

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/dba-scripts-get-database-summary/)
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,
        /* Copy-only log backups do not truncate the log, so they must not satisfy the
           WARN:log-backup-overdue test below. Copy-only fulls stay counted above. */
        MAX(CASE WHEN type = 'L' AND is_copy_only = 0 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:

• sql/inventory/Get-DatabaseSummary.sql
• powershell/wrappers/inventory/Get-DatabaseSummary.ps1

Example Output

Understanding the Results

The capture above shows the shape you are reading for. The four system databases sit at the top with a blank notes column, because every backup check is scoped to database_id > 4 and system databases are not expected to carry user backups. Below them, the user databases are the ones worth your attention: same recovery model, same owner, but different backup dates, and it is the gap between those dates and today that the notes column turns into a warning.

One thing the capture cannot show you is that notes column itself, which sits off the right edge of the grid. It is the column the script exists for, so scroll to it first rather than reading the database list left to right.

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

And the columns themselves, one row per database:

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.

Microsoft’s reference covers sys.master_files and sys.databases in full.

Frequently Asked Questions

Related Scripts

You may also find these scripts useful:

• Database Inventory
• Databases
• Database Snapshot Inventory
• Database Health
• Job Inventory
• Linked Server and Job Inventory
• Linked Server Inventory
• Login Inventory
• OS and Hardware Info
• Patch Level
• Server Inventory (pillar overview)
• Services Information
• DBA Scripts: The Complete Guide, the map across every script on this site

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:log-backup-overdue 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.