DBA Scripts: Get Database Health

---

Before diving into anything deeper, a health check should start with the basics: is every database actually online, is the recovery model what it’s supposed to be, and is anything quietly stuck waiting for a log backup it isn’t getting. None of these are exotic checks, but they’re exactly the kind of thing that goes unnoticed for months on an instance nobody’s watching closely, because a database in SUSPECT state or stuck on LOG_BACKUP doesn’t necessarily throw an alert anywhere.

This script is the one-query overview: state, recovery model, log reuse wait reason, access mode, three risky settings, and current data/log size, for every user database on the instance in one pass.

---

Why Database Health Matters

Each column here answers a specific operational question that, left unchecked, turns into an incident:

• state_desc not ONLINE means a database is unavailable right now, RECOVERY_PENDING, SUSPECT, and OFFLINE all mean something has already gone wrong.
• recovery_model_desc determines what backup strategy is even possible. A database quietly running SIMPLE when the business expects point-in-time recovery is a gap that only surfaces during an actual restore, the worst possible time to find out.
• log_reuse_wait_desc not NOTHING means the transaction log can’t reclaim space until whatever it’s waiting on clears, most commonly a missing log backup, which left unresolved leads straight to log file growth and eventually a full transaction log.
• is_auto_shrink_on is a setting Microsoft itself recommends against, it causes the log and data files to shrink and then immediately grow again under normal usage, generating fragmentation and I/O for no benefit.
• is_auto_close_on closes and reopens the database’s files after every connection drops to zero, adding startup latency to the next connection, rarely appropriate outside single-user desktop scenarios.

---

Common Symptoms

• A transaction log that keeps growing despite regular log backups appearing to run.
• A database discovered offline or in recovery only when an application throws a connection error.
• Inconsistent backup behaviour traced back to a database that’s on SIMPLE recovery when everyone assumed FULL.
• Unexplained I/O spikes at regular intervals, sometimes a symptom of auto-shrink fighting normal growth.

---

When to Run This Script

• Routine SQL Server health checks, this is a good first query in any check, before the deeper diagnostics
• Getting to know a new or inherited instance for the first time
• Investigating unexpected transaction log growth
• After any change to backup jobs or maintenance plans, to confirm recovery model and log reuse status match expectations

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DatabaseHealth
Category    : maintenance-and-reliability
Purpose     : Review the health and sizing posture of user databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-database-health/)
Requires    : VIEW ANY DATABASE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    d.name AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    d.user_access_desc,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    ROUND(CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END) * 8.0 / 1024 AS DECIMAL(18,2)), 1) AS data_size_mb,
    ROUND(CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END) * 8.0 / 1024 AS DECIMAL(18,2)), 1) AS log_size_mb
FROM sys.databases AS d
LEFT JOIN sys.master_files AS mf
    ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    d.user_access_desc,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_auto_shrink_on
ORDER BY d.name;

This queries sys.databases joined to sys.master_files, returning one row per user database with its current state, recovery model, log reuse wait reason, three risky configuration flags, and total data and log size in MB.

---

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

# Review state, recovery model, and risky settings for every database:
.\run.ps1 Get-DatabaseHealth

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

This script lives in the repo at:

• sql/monitoring/databases/Get-DatabaseHealth.sql
• powershell/wrappers/monitoring/databases/Get-DatabaseHealth.ps1

---

Example Output

Real output captured against a local SQL Server 2025 instance. Two databases here, GrowthLab and RandomLab, show log_reuse_wait_desc = LOG_BACKUP, a genuine, unmanufactured finding: both are on FULL recovery without a scheduled log backup job, exactly the pattern this check exists to catch before the log grows unbounded.

---

Understanding the Results

• state_desc should read ONLINE for every row. Anything else, RECOVERY_PENDING, SUSPECT, OFFLINE, RESTORING, is an active problem, not a warning sign of a future one.
• log_reuse_wait_desc is the most actionable column here. NOTHING means the log can reclaim space freely. Any other value names exactly what’s blocking reuse, LOG_BACKUP (no recent log backup), ACTIVE_TRANSACTION (a long-running transaction), REPLICATION, or DATABASE_MIRRORING among others. In the output above, GrowthLab and RandomLab are both stuck on LOG_BACKUP, on FULL recovery, the log won’t shrink back down until a log backup runs, and if none is scheduled, that log file only grows from here.
• recovery_model_desc should match what your backup strategy assumes. FULL without log backups is the classic gap, you get point-in-time recovery in theory, but the log grows unchecked until someone notices, and disaster recovery testing is the wrong time to discover it.
• is_auto_shrink_on and is_auto_close_on should both read False on essentially every production database. Both are legacy settings largely inappropriate outside single-user desktop scenarios.

---

Common Causes

LOG_BACKUP reuse waits on FULL recovery databases almost always trace back to the same root cause: someone (correctly) put the database on FULL recovery for point-in-time restore capability, but the log backup job either was never created, silently started failing, or was scoped to exclude a newly added database. The database keeps taking full backups on schedule, which look reassuring in a job history report, while the log itself just keeps growing between them.

---

How to Fix Database Health Issues

Missing log backups on FULL recovery: either add a scheduled log backup job, or, if point-in-time recovery genuinely isn’t needed for that database, switch it to SIMPLE:

-- Option A: take a log backup now, then schedule regular ones
BACKUP LOG RandomLab TO DISK = 'D:\Backups\RandomLab_log.trn';

-- Option B: if point-in-time recovery isn't needed, switch to SIMPLE
ALTER DATABASE RandomLab SET RECOVERY SIMPLE;

Auto-shrink or auto-close enabled:

ALTER DATABASE RandomLab SET AUTO_SHRINK OFF;
ALTER DATABASE RandomLab SET AUTO_CLOSE OFF;

---

Best Practices

• Run this script as the first step in any health check, it surfaces availability and backup-chain problems before you go looking at performance detail.
• Never leave a FULL recovery database without a matching log backup job, if you can’t commit to log backups, the database should be on SIMPLE.
• Leave auto-shrink and auto-close off on every production database, both cause more harm (fragmentation, connection latency) than the disk space or resource saving is worth.
• Re-run periodically, not just at initial setup, jobs get disabled, recovery models get changed during troubleshooting and never changed back, and new databases get added without inheriting the standard configuration.

---

Related Scripts

You may also find these scripts useful:

• Suspect Pages and Integrity Checks
• Last DBCC CHECKDB
• Database Inventory
• Database Summary
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Common Questions

Summary

None of the columns in this script are individually exotic, state, recovery model, log reuse wait, a couple of legacy settings. What makes it worth running regularly is that each one, left unchecked, becomes a real incident: an offline database nobody noticed, a log file that grew to fill the drive, a restore that couldn’t hit the point in time it needed to.

Run it as the opening move of any health check, and treat anything other than ONLINE and NOTHING in the state and log-reuse columns as worth a closer look before moving on to deeper diagnostics.