SQL Server Health Check Checklist

Backup Strategy and Verification

• Full backups are running for all user databases
• Transaction log backups are running for databases in FULL or BULK_LOGGED recovery model
• Differential backups are configured where appropriate to reduce restore time
• Backup jobs have completed successfully within the last expected window
• Backup files are being written to a location separate from the data and log drives
• Backup files are being copied off-server or off-site (network share, cloud, tape)
• A test restore has been performed within the last 90 days
• msdb backup history is not being pruned so aggressively that recent history is unavailable
• Backup compression is enabled where supported
• Alerts or notifications exist for backup job failures

SQL Agent Jobs and Alerts

• SQL Server Agent is running and set to auto-start
• All enabled jobs have run within their expected schedule
• Job failure history has been reviewed — no silent failures
• Operator email addresses are configured and valid
• Database Mail is configured and sending successfully
• Critical alerts (severity 17–25, error 823, 824, 825) are defined and routed to an operator
• The SQL Server Agent error log is being reviewed periodically
• Jobs that overlap in schedule have been reviewed for contention

Security and Access

• The sa account is disabled or renamed
• SQL Server authentication mode is documented and appropriate (Windows vs. Mixed)
• Logins without corresponding users (orphaned logins) have been reviewed
• Sysadmin role members are limited to those who require it
• Linked servers are documented and restricted to necessary permissions
• Service accounts are using least-privilege domain accounts, not local system
• Windows Firewall or network controls limit SQL Server port exposure
• Guest user is disabled in all user databases
• Audit logging is in place where required by compliance policy

Performance Baselines

• Top wait stats have been reviewed (sys.dm_os_wait_stats) and dominant waits are understood
• Missing index DMVs have been reviewed (sys.dm_db_missing_index_details)
• Top queries by CPU and logical reads have been identified
• Query Store is enabled on SQL Server 2016+ databases
• Parameter sniffing issues have been investigated if query performance is inconsistent
• No single query accounts for a disproportionate share of tempdb usage
• Blocking and deadlock history has been reviewed
• Page Life Expectancy is within an acceptable range for the server's buffer pool size
Indexes

Index Health

• Index fragmentation has been reviewed — high-fragmentation indexes have been rebuilt or reorganized
• Index maintenance jobs (rebuild/reorganize) are scheduled and completing successfully
• Statistics update jobs are scheduled
• Unused indexes have been identified (sys.dm_db_index_usage_stats)
• Duplicate or redundant indexes have been reviewed and consolidated where safe
• Tables with no clustered index (heap tables) have been reviewed
Configuration

Instance Configuration

• Max server memory is set — SQL Server is not consuming all available RAM
• MAXDOP is configured appropriately for the server's core count
• Cost threshold for parallelism is set above the default of 5
• TempDB has multiple equally-sized data files (one per logical core, up to 8)
• TempDB data and log files are on a dedicated drive separate from user databases
• Data files are not configured with autogrowth in percentage — fixed MB increments are preferred
• Instant file initialization is enabled for the SQL Server service account
• Lock pages in memory is configured where appropriate
• Database consistency checks (DBCC CHECKDB) are running and completing without errors
• The SQL Server error log is being reviewed — no recurring errors going unaddressed
HA/DR

High Availability and Disaster Recovery

• Recovery objectives (RPO and RTO) are documented
• Current HA/DR configuration matches documented recovery objectives
• AG synchronization state is SYNCHRONIZED for synchronous replicas
• AG secondary replicas are readable where needed and backup preferences are configured
• AG listener is configured and DNS resolution is verified
• A planned failover has been tested in the last 12 months
• Log shipping or replication lag is within acceptable thresholds if in use
• A documented runbook exists for failover and recovery procedures