A database rarely fails all at once. It degrades quietly - backups that never get restore-tested, indexes fragmenting month after month, statistics drifting out of date - until one busy morning the whole thing tips over. A scheduled health check catches that decay while it is still cheap to fix.
This guide walks through a practical, repeatable database health check that a DBA, a developer, or a hands-on engineering manager can run in an afternoon. It covers the eight areas that actually matter, gives you a diagnostic query where one helps, and finishes with a checklist and a scorecard you can reuse each quarter. The examples use SQL Server T-SQL, but every concept maps cleanly to PostgreSQL, MySQL, and Oracle.
What this guide covers
- What a health check is, and why quarterly
- Backups & recovery
- Data integrity
- Index health
- Statistics freshness
- Query performance baselines
- Security & permissions
- Capacity & growth
- Configuration & maintenance jobs
- How often to run each check
- Turning findings into an action plan
- Automating the health check
- The checklist, scorecard & signal table
What a health check is, and why quarterly
A database health check is a structured audit of the systems that keep your data safe, consistent, and fast. It is not firefighting. You run it when nothing is on fire precisely so that you can see the slow-moving problems that no alert will page you about: a restore that has silently stopped working, a table growing 12% a month toward a disk wall, an index that hasn't been useful since a query was rewritten two releases ago.
Quarterly is the sweet spot for most systems. Monthly is overkill for a stable OLTP database and turns the check into a chore that gets skipped. Annually is too slow - fragmentation, growth, and permission sprawl all compound faster than a year. Run it more often around major releases, data migrations, or after a big traffic change. If you are planning a move between platforms or environments, pair the check with a database migration checklist so nothing carries the rot forward.
Treat the output as a trend, not a snapshot. A single fragmentation number means little; the same number measured every quarter tells you whether your maintenance is keeping up. Save each run so you can compare.
Backups & recovery
Start here, always. Everything else on this list protects performance or convenience; backups protect the business. The uncomfortable truth is that most teams have backups and almost none of them regularly prove those backups restore.
What to look for
- RPO (Recovery Point Objective) - how much data you can afford to lose, in time. If your RPO is 15 minutes, transaction-log backups must run at least that often.
- RTO (Recovery Time Objective) - how long a full restore actually takes. Measure it; do not assume it.
- Restore tests - an untested backup is a hope, not a plan. Restore to a scratch server at least once a quarter and verify row counts.
- Coverage - every user database in FULL or BULK_LOGGED recovery has a current log-backup chain; nothing important sits in SIMPLE by accident.
Confirm when each database was last backed up and how big the recovery gap is:
-- Last full / diff / log backup per database, and hours since
SELECT d.name,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log,
DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) AS hrs_since_any
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY hrs_since_any DESC;Watch out: a green backup job does not mean a green restore. Corruption can be backed up faithfully. The only proof is an actual restore that opens and passes a consistency check.
Data integrity
Storage hardware, drivers, and rare engine bugs can corrupt pages on disk. You want to find that corruption on your schedule, from a clean backup you still have - not three weeks later when the corrupt page happens to be read and the backup chain no longer covers it.
What to look for
- DBCC CHECKDB runs clean on every database, on a regular schedule.
- Zero allocation or consistency errors in the output and in the SQL Server error log.
- Suspect pages table is empty (
msdb.dbo.suspect_pages). - Page verification is set to CHECKSUM on every database, not the legacy TORN_PAGE_DETECTION or NONE.
-- Consistency check; DATA_PURITY catches out-of-range values too
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
-- Any page ever flagged as corrupt?
SELECT * FROM msdb.dbo.suspect_pages;On very large databases where a full CHECKDB is too heavy for the maintenance window, split it: run CHECKDB against a restored copy on another server (which doubles as your restore test), or rotate through CHECKTABLE on subsets. Never skip it entirely.
Index health
Indexes are the biggest lever most teams have over query speed, and they rot in three directions at once: they fragment, they go unused, and the ones you need never get created. A health check inspects all three. For the fundamentals of how indexes work, see the guide on SQL indexes.
Fragmentation
As rows are inserted, updated, and deleted, index pages split and scatter. High logical fragmentation makes range scans read more pages than they should. The common rule of thumb: reorganize between 10% and 30% fragmentation, rebuild above 30%, and ignore anything under 10% or smaller than about 1,000 pages (the overhead outweighs the gain).
-- Fragmentation for indexes worth acting on (>1000 pages, >10% frag)
SELECT OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS frag_pct,
ips.page_count,
CASE WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
ELSE 'REORGANIZE' END AS action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
AND ips.page_count > 1000
AND i.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;Unused and duplicate indexes
Every index is a tax on writes and on storage. Indexes that are never read but constantly updated are pure cost. Compare reads (seeks, scans, lookups) against writes:
-- Indexes written to but rarely read since last restart
SELECT OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
s.user_seeks + s.user_scans + s.user_lookups AS reads,
s.user_updates AS writes
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
AND i.index_id > 1
AND s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;Missing indexes
The engine records indexes it wishes it had. Treat these as hints, not orders - the DMV over-suggests wide covering indexes and never accounts for write cost - but a suggestion with a huge improvement measure that keeps reappearing is worth acting on. Cross-check candidates against your slowest queries before you create anything.
Statistics freshness
The query optimizer chooses plans based on statistics: histograms that estimate how many rows a predicate will return. When statistics go stale - often after bulk loads or on large, fast-growing tables - the optimizer guesses wrong and picks bad plans, and no amount of indexing fixes a plan built on a lie.
What to look for
- Auto-update statistics is ON for the database (it usually should be).
- Large tables may still need manual, more-frequent updates because the auto threshold scales with row count.
- Sample rate is high enough on skewed columns - a low automatic sample can miss the distribution.
-- Stats not updated recently, ordered by staleness
SELECT OBJECT_NAME(s.object_id) AS table_name,
s.name AS stat_name,
sp.last_updated,
sp.rows,
sp.modification_counter AS rows_changed
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
ORDER BY sp.last_updated ASC;If many rows have changed since last_updated, schedule an UPDATE STATISTICS ... WITH FULLSCAN during a quiet window for the worst offenders.
Query performance baselines
You cannot tell whether the database is slow without a baseline of what normal looks like. The health check captures the current top resource consumers so that next quarter you can see what changed. Rank queries by total logical reads (I/O) and by total worker time (CPU); the two lists rarely match, and both matter.
-- Top 20 statements by total logical reads since cache load
SELECT TOP 20
SUBSTRING(t.text, (qs.statement_start_offset/2)+1, 120) AS stmt,
qs.execution_count AS execs,
qs.total_logical_reads AS total_reads,
qs.total_logical_reads / qs.execution_count AS avg_reads,
qs.total_worker_time / 1000 AS cpu_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_logical_reads DESC;Record the top offenders and their average reads. A query that jumps from 800 to 40,000 average reads between checks is your next investigation, even if users haven't complained yet. For the systematic follow-up, work through the performance tuning checklist or the broader performance tuning guide.
Security & permissions
Permissions sprawl the same way indexes do - someone needs access for a one-off task, it never gets revoked, and three years later half the org can write to production. The health check is where you claw least privilege back. For the full treatment, see the SQL security lesson.
What to look for
- Least privilege - application logins have only the rights they use; no everyday account is in
sysadminordb_ownerwithout a reason. - Orphaned users - database users whose server login no longer exists, common after restores between servers.
- Encryption - TLS in transit, and Transparent Data Encryption or column encryption at rest where policy or regulation requires it.
- Auditing - logins, permission changes, and access to sensitive tables are actually logged.
-- Orphaned users: DB users with no matching server login
SELECT dp.name AS db_user, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')
AND sp.sid IS NULL
AND dp.authentication_type > 0;Go beyond the account list. Two checks catch the most common real-world exposures. First, list everyone with instance-wide power, because that membership is where a breach does the most damage:
-- Logins in high-privilege server roles
SELECT sp.name AS login_name, r.name AS server_role
FROM sys.server_role_members m
JOIN sys.server_principals r ON r.principal_id = m.role_principal_id
JOIN sys.server_principals sp ON sp.principal_id = m.member_principal_id
WHERE r.name IN ('sysadmin', 'securityadmin', 'serveradmin')
ORDER BY r.name;Second, confirm the boring hygiene that audits fail on: SQL logins with CHECK_POLICY off (weak or non-expiring passwords), the sa account renamed or disabled, the public role not granted anything sensitive, and no leftover access on system databases. Note the last successful DBCC-clean date, the last permission review date, and whether encryption (TLS in transit, TDE at rest) is actually on rather than merely licensed. Record who has access to personally identifiable or financial columns; regulations like GDPR and PCI DSS want that list to be short and justified.
Capacity & growth
Running out of disk is one of the few database failures that stops writes cold and is entirely predictable. Capacity is about trend, not today's number.
What to look for
- Data and log size trend - how fast each file has grown over the last few checks, and when it will hit the drive limit at that rate.
- Free disk on data, log, and tempdb volumes, with alerting well before full.
- Autogrowth settings - growth by a sensible fixed size (for example 256 MB or 512 MB), never a tiny default or a percentage that balloons on large files. Percentage growth on a 200 GB file is a landmine.
- Instant file initialization is enabled so data-file growth doesn't stall writes.
-- File sizes, space used, and autogrowth config
SELECT name AS logical_name,
type_desc,
size / 128 AS size_mb,
FILEPROPERTY(name, 'SpaceUsed') / 128 AS used_mb,
CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR) + ' %'
ELSE CAST(growth / 128 AS VARCHAR) + ' MB' END AS autogrowth
FROM sys.database_files;Turn the raw numbers into a runway. If a 200 GB data file is growing 8 GB a month and the drive has 96 GB free, you have roughly twelve months - plan the expansion now, not the week it fills. The most useful capacity artifact is a small table of file size captured at each quarterly check; the slope between rows is your forecast. Watch tempdb and the transaction log separately from user data: tempdb blowouts usually mean a bad plan spilling to disk, and a log that never shrinks usually means a broken backup chain or a long-running open transaction, not real data growth. Don't forget backup storage in the capacity picture - a retention policy that keeps more history than the backup drive can hold fails exactly when you need the oldest restore point.
Configuration & maintenance jobs
Finally, confirm the instance is configured sanely and that the jobs meant to keep it healthy are actually succeeding. This is where good defaults quietly prevent whole categories of problems.
- Max server memory is capped so the OS is never starved.
- tempdb has multiple equally sized data files and lives on fast storage.
- MAXDOP and cost threshold for parallelism are set deliberately, not left at the ancient defaults.
- Maintenance jobs - backups, integrity checks, index and statistics maintenance - all have recent successful run history, and failures raise an alert.
- Error log is reviewed for recurring warnings (long I/O waits, memory dumps, login failures).
A job that is scheduled but failing silently is worse than no job at all, because it creates false confidence. Check the outcome, not just the existence, of every maintenance job.
How often to run each check
The full audit is quarterly, but the individual pieces run on very different natural cadences. Some are continuous background jobs; some are once-a-quarter deep dives; a few only make sense around specific events. Matching each check to the right frequency keeps the quarterly review light because most of the work is already happening automatically.
| Check | Recommended cadence | Why that rhythm |
|---|---|---|
| Log & differential backups | Continuous (minutes / daily) | Driven directly by your RPO |
| Full backups | Daily or weekly | Balances restore time against storage |
| Restore test | Monthly to quarterly | The only proof backups work |
| DBCC CHECKDB | Weekly (nightly on critical DBs) | Catches corruption while backups still cover it |
| Index & statistics maintenance | Weekly automated job | Fragmentation and stats drift steadily |
| Query baseline capture | Quarterly, plus each release | Detects regressions against a known-good point |
| Permission & security review | Quarterly, plus on staff change | Access sprawls with every one-off grant |
| Capacity forecast | Quarterly, monthly if near a limit | Growth is predictable if you watch it |
| Config drift review | Quarterly, plus after any patch | Upgrades and hotfixes reset settings |
The pattern is simple: anything that protects data or degrades daily (backups, integrity, index and statistics maintenance) should be an automated job that runs far more often than the audit. The quarterly health check then becomes a review of whether those jobs are still succeeding, plus the slower analyses - security, capacity, and configuration - that genuinely only need doing a few times a year. Add an unscheduled check after any major event: a version upgrade, a large data migration, a hardware move, or a sudden change in traffic.
Turning findings into an action plan
A health check that ends in a list of problems is only half done. The value is in what you fix and in what order. The trap is treating every red bar as equally urgent and either burning a week on cosmetic fragmentation or, worse, freezing because the list looks overwhelming. Prioritize by two axes: the risk if you do nothing, and the effort to fix.
Rule of thumb: sort findings into four buckets - high risk / low effort first (fix today), high risk / high effort next (plan and schedule), low risk / low effort as quick wins when you have a spare hour, and low risk / high effort last (often just document and defer).
Risk means blast radius, not annoyance. A backup chain that cannot restore is catastrophic and usually cheap to fix, so it goes to the front regardless of anything else. A corrupt page found by CHECKDB is next. Then comes anything trending toward a hard wall - a disk that fills in two months, a log that never clears. Only after the survival items do you spend time on the optimizations: rebuilding fragmented indexes, dropping unused ones, refreshing statistics, tuning the queries whose average reads spiked. Cosmetic items - a slightly stale statistic on a small table, 12% fragmentation on a lookup table - can wait for the automated jobs to handle them.
Write down every finding with a severity
One row per issue: area, what you found, the number, and a red / amber / green rating from the scorecard.
Score risk and effort
Rate each finding high or low on both. Recoverability and integrity issues are high risk by default.
Fix the high-risk, low-effort items immediately
Repair the backup chain, enable CHECKSUM, remove a dangerous permission - these are same-day changes with outsized payoff.
Schedule the high-effort work
Disk expansion, an index redesign, or an encryption rollout gets an owner and a date, not a vague intention.
Assign an owner and a deadline to each item
An action plan without a name and a date beside each row does not get done.
Re-check next quarter
Carry the open items forward so the scorecard shows whether you are gaining or losing ground.
Keep the plan short and blunt so a manager can read it and a developer can act on it. Two or three genuinely fixed items per quarter beats a twenty-line wish list that never moves. If the high-effort work outstrips the time you have, that is a concrete case for bringing in help - see when it makes sense to hire a SQL consultant.
Automating the health check
Everything in this guide can and should be scripted, because a check that depends on someone remembering to run it will eventually be forgotten. The goal is that the routine work runs itself, alerts you when something breaks, and leaves you a clean report to review each quarter rather than a blank query window.
Scheduled maintenance and checks
Use SQL Server Agent (or cron plus sqlcmd on Linux) to schedule the recurring pieces. Rather than hand-rolling scripts, the widely used Ola Hallengren Maintenance Solution handles backups, integrity checks, and index and statistics maintenance with sensible thresholds already built in. Wrap each job so its outcome is recorded and a failure is loud.
-- Example Agent job step: integrity check with alert on failure
EXEC dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@LogToTable = 'Y';
-- On failure the step should raise a high-severity error so the
-- operator gets paged; never let CHECKDB fail quietly.Collect results into a table
Point the diagnostic queries from earlier at a small central database and stamp each row with a run date. Over a few quarters this becomes a history you can trend - fragmentation over time, growth per file, the moving top-ten queries - without any manual bookkeeping.
-- Snapshot key file sizes into a history table each run
INSERT INTO ops.CapacityHistory (run_date, db_name, file_name, size_mb, used_mb)
SELECT GETDATE(), DB_NAME(), name,
size / 128, FILEPROPERTY(name, 'SpaceUsed') / 128
FROM sys.database_files;Alerting on the things that cannot wait
Automation is only useful if it interrupts you when it matters. Configure alerts for the failures that a quarterly review is far too slow to catch: any DBCC CHECKDB error or new suspect page, a failed or missed backup, free disk dropping below a threshold, and severity 17 or higher errors in the log. Route these to email, a pager, or a chat channel - wherever your team actually looks. Everything else can stay in the report.
Watch out: automation does not replace the human review. Scripts capture numbers; they do not decide that a 40% fragmented index on a table you just archived can be dropped instead of rebuilt. Keep the quarterly read-through - let the machines gather, and you judge.
The checklist, scorecard & signal table
Run the eight areas above as a repeatable process each quarter. Here it is as a sequence of steps.
Verify recoverability
Confirm every database has a current backup chain that meets its RPO, then restore at least one to a scratch server and check row counts against production.
Check integrity
Run DBCC CHECKDB clean on every database, confirm CHECKSUM page verification, and check that suspect_pages is empty.
Audit indexes
Capture fragmentation, unused and duplicate indexes, and the recurring missing-index suggestions. Rebuild, reorganize, or drop as the numbers dictate.
Refresh statistics
Update stats on large or heavily modified tables, and confirm auto-update is on.
Baseline performance
Record the top queries by reads and CPU and compare against last quarter's baseline.
Review security
Re-apply least privilege, remove orphaned users, and confirm encryption and auditing meet policy.
Project capacity
Chart data, log, and disk growth, and confirm autogrowth is a sane fixed size on every file.
Confirm config & jobs
Validate memory, tempdb, and parallelism settings, and confirm every maintenance job has recent successful runs.
Score each area from the queries above, then plot the result. A scorecard like the one below turns eight technical checks into a picture anyone - including a manager - can read in five seconds. The bars below are representative of a database that is healthy on the essentials but carrying index and capacity debt.
The scorecard tells you where to spend the next quarter: here, index maintenance and capacity planning. Backups and integrity are solid, so leave them alone. The point of the scorecard is triage - a red bar earns attention, a green bar earns none.
How to test each area and read the signal
Use this table as the reference for the whole check: what to run, and how to tell a healthy result from a warning sign.
| Check | How to test | Green signal | Red signal |
|---|---|---|---|
| Backups | Query backupset; run a real restore | Chain meets RPO; restore verified | Missing log chain; no restore ever tested |
| Integrity | DBCC CHECKDB; suspect_pages | Zero errors; CHECKSUM on | Any allocation/consistency error |
| Fragmentation | dm_db_index_physical_stats | Large indexes under 10% | Many large indexes over 30% |
| Unused indexes | dm_db_index_usage_stats | Reads justify write cost | Zero reads, heavy writes |
| Statistics | dm_db_stats_properties | Fresh on active tables | Stale after big data change |
| Query baseline | dm_exec_query_stats | Top queries stable vs last check | Avg reads spiking on key queries |
| Permissions | Principals + role membership | Least privilege; no orphans | Broad sysadmin; orphaned users |
| Capacity | database_files; disk free | Fixed autogrowth; runway > 12 mo | Percent growth; drive near full |
Assign each area a simple weight if you want a single headline number - but weight recoverability and integrity heavily, because a fast database you cannot restore is worthless. The reference weights below keep the scorecard honest.
| Area | Suggested weight | Why |
|---|---|---|
| Backups & recovery | 25% | Loss here is unrecoverable |
| Data integrity | 20% | Corruption spreads and hides |
| Capacity & growth | 15% | A full disk stops writes |
| Index health | 10% | Biggest lever on speed |
| Query baselines | 10% | Detects regressions early |
| Security | 10% | Breach and compliance risk |
| Statistics | 5% | Feeds good query plans |
| Config & jobs | 5% | Prevents whole failure classes |
A healthy baseline: backups meet RPO and pass a real restore test, DBCC CHECKDB runs clean, large indexes stay under 10% fragmentation with no unused indexes bleeding writes, statistics are fresh, top queries are stable quarter over quarter, permissions follow least privilege with no orphaned users, every file has runway and fixed autogrowth, and every maintenance job shows recent success. Hit that and your database is genuinely healthy - keep the report and compare next quarter.
Health checks pay for themselves the first time one catches a broken restore before you needed it. If you would rather have an expert run the first pass and set up the recurring process, a SQL developer can do it in a day and hand you the scorecard.