SQL Server Health Check: What It Should Cover (12-Area Checklist)

A SQL Server environment can look healthy for years, right up to the day a backup turns out to be unusable when you need to restore it, a disk fills up, or corruption nobody noticed comes to light. A SQL Server health check is a structured review that makes these risks visible before they turn into incidents.

This guide covers which areas a good health check should cover, what to look at in each one, and how to run some of the checks yourself. If you’re getting a health check from a service provider, you can also use this list to compare the scope and output you should expect.

When Should You Run a Health Check?

• When the DBA who ran the environment leaves, or a new team takes over.
• Before an upgrade, migration or hardware refresh.
• When there are chronic performance complaints but nobody knows why.
• Before an audit, cybersecurity assessment or regulatory review.
• At least once a year, even without complaints.

The 12 Areas a Good Health Check Covers

1. Backups and restores

For each database: the time of the last full, differential and log backup, whether the recovery model matches the backup strategy, whether backups sit on the same server or somewhere else, and above all whether restores are tested regularly.

SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.database_id <> 2
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full;

Databases in the FULL recovery model without log backups are one of the problems we find most often in the field: the log file keeps growing until it eventually fills the disk.

2. Data integrity (DBCC CHECKDB)

Check when the last successful CHECKDB ran for each database, and whether errors 823, 824 and 825 appear in the error log. The later corruption is noticed, the smaller the chance of restoring a clean backup.

SELECT name, DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS last_good_checkdb
FROM sys.databases
WHERE state_desc = N'ONLINE' AND database_id <> 2
ORDER BY last_good_checkdb;

3. Version, build and support status

Check whether the SQL Server and Windows versions are still supported, which cumulative update is installed, and whether it’s a known problematic build. An out-of-support version doesn’t get security patches.

SELECT SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('ProductLevel')   AS level,
       SERVERPROPERTY('ProductUpdateLevel') AS cu,
       SERVERPROPERTY('Edition')        AS edition;

4. Instance configuration

Max server memory, MAXDOP, cost threshold for parallelism, Instant File Initialization, Lock Pages in Memory, legacy settings such as priority boost, and every non-default sp_configure value are assessed against the workload.

5. Security

Review sysadmin members, the state of the sa account, SQL logins without a password policy, features such as xp_cmdshell left enabled, service account permissions and the audit configuration.

6. TempDB

Check the number of data files, equal sizing and growth settings, separate disk placement and signs of contention.

7. Storage and files

Review disk latency, free space, autogrowth settings (percentage growth or tiny increments), the VLF count in log files and how files are placed across disks. For disk latency:

SELECT DB_NAME(vfs.database_id) AS database_name, mf.physical_name,
       vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)  AS avg_read_ms,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
     ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

8. Wait statistics

Wait statistics show what the server spends its time on (CPU, disk, locks, memory, network). That tells you where to look for performance problems.

9. Indexes and statistics

Review missing, unused and duplicate indexes, heaps, the state of maintenance and stale statistics.

10. SQL Server Agent and jobs

Check failed jobs, jobs owned by people who have left, maintenance jobs that start at the same time and collide, and notification (operator) settings.

11. High availability and disaster recovery

Assess the synchronization state of Always On availability groups, failover readiness, login and job differences between replicas, and whether RPO and RTO targets are actually met.

12. Monitoring and alerts

Check whether alerts exist for severity 17–25 errors and for 823/824/825, whether Database Mail works, and whether alerts reach the right people.

The Problems We Find Most Often

In our experience, the same themes come up in health check after health check:

• No backup policy, leading to full log files and stalled replication.
• CHECKDB never run, or not run for years, so corruption is found late.
• Disk layouts that ignore best practices, and disks that fill up at peak hours combined with poor autogrowth settings.
• No index maintenance at all, or every index rebuilt every night for no reason.
• Applications that never close connections and pile up sleeping sessions, and transaction handling that causes deadlocks.
• Always On and replication performance that nobody watches.

What to Expect from a Service Provider

• Read-only work: nothing should be changed in the environment during the health check, and it shouldn’t add load to the production workload.
• Transparency: the scripts that run should be visible, and what data is collected should be stated clearly.
• Configuration, not data: customer data shouldn’t be read or taken out of the environment.
• A prioritized report: risk, impact, recommended fix and estimated effort for every finding.
• Repeatability: after fixes, the same checks should be rerun so progress can be measured.

How Aryasoft Does It

For health checks, Aryasoft uses HELT360, an automated health check tool it developed as an R&D project at GarajX TEKMER:

• HELT360 runs read-only, performs configuration checks only and adds no load to the server.
• Every script it runs is visible in its panel; on request, all of its traffic is logged to text files.
• Findings are reported with timestamps and prioritized; after fixes, the same scan is rerun to measure progress.
• On top of the automated scan, our senior DBAs interpret the workload, wait statistics and architecture; the report gives managers a clear summary as well as the technical team.

For customers who want it, the health check is the first step of our managed database service: findings turn into a prioritized work plan, and fixes are applied without disruption.