A practical SQL Server maintenance checklist

Production SQL Server estates rarely fail because someone forgot a fancy feature. They fail because basics drift: backups untested, integrity checks skipped, indexes either ignored or rebuilt every night “just in case”.

This checklist is the foundation we use at Allay Data Solutions when assessing an environment.

1. Backups you can restore

Ask three questions for every critical database:

What is the recovery model? Full recovery without log backups is a silent risk.
What is the RPO? How much data can the business afford to lose?
When did you last restore? A backup file that never restores is hope, not a plan.

Minimum pattern for most OLTP systems:

Full backups on a schedule that matches growth and change rate
Differential backups if fulls are large/slow
Transaction log backups frequently enough for your RPO
Off-box / off-site copies
Periodic restore drills with documented timings

2. Integrity checks

DBCC CHECKDB is not optional for systems that matter. Corruption that sits unnoticed becomes a restore-from-last-good-backup event — often at the worst possible time.

Practical guidance:

Run CHECKDB regularly (weekly is common; more often for high-value systems)
Prefer native CHECKDB over “we rely on storage checksums only”
Monitor for failures and alert hard when it fails
Size resources so CHECKDB can complete in the maintenance window

3. Index and statistics maintenance

Fragmentation is not the only metric. Blind nightly rebuilds waste I/O and can hurt plan stability.

Better approach:

Update statistics based on data change patterns
Rebuild/reorganise indexes when fragmentation and page counts justify it
Avoid one-size-fits-all scripts without thresholds
Watch for duplicate and unused indexes that add write cost

4. Agent jobs and alerting

Your maintenance plan is only as good as your failure notifications.

Every critical job should alert on failure (email/Teams/monitoring tool)
Failed jobs older than “we’ll notice eventually” are operational debt
Include long-running job detection, not only fail/succeed

5. Housekeeping that people forget

SQL Agent history retention
Backup history cleanup (msdb growth is a classic surprise)
Error log cycling
Drive space and Autogrowth settings that will not freeze production mid-day
Certificate / credential / Linked Server documentation

A simple weekly rhythm

Cadence	Focus
Daily	Log backups, job failures, disk space, error log spikes
Weekly	CHECKDB (or subset strategy), index/stats maintenance, review top waits
Monthly	Restore drill sample, security patch window planning, capacity trend
Quarterly	Full health check: config, HA/DR, permissions, performance baseline

How we help

If you want this turned into a documented, monitored plan for your estate — not a generic script dump — contact Allay Data Solutions. We design maintenance that matches your RPO/RTO and operational capacity, whether you have one instance or many.