SQL Server Health Check: Six Checks to Prioritize

A SQL Server health check becomes useful when its findings lead to a clear, justified next step. I start with recovery and access risks before tuning preferences. A warning deserves investigation, but it does not automatically justify a server change.

1. Can the backups meet the recovery requirement?

Compare successful backups with the business’s accepted data loss and recovery time. Check the necessary full, differential and log backup sequence for the chosen recovery model. A successful job entry alone does not show that the required files are still accessible.

Restore a representative backup sequence to a separate test destination and verify the resulting database. Record how long recovery takes and what the application needs afterward. RESTORE VERIFYONLY checks backup readability and completeness, but it does not restore or validate the database’s internal structure.

2. Can one storage failure remove data and backups?

Check the physical storage behind the database files and backup destinations. Two folders or drive letters can still depend on the same physical device. Keep backups on a separate physical location or device.

Ask what happens if the underlying storage disappears, rather than judging only the path names. Include remote backup availability and the permissions needed during recovery. Document that dependency before moving files or redesigning storage.

3. Does tempdb fit the workload?

Review the data-file sizes, growth increments, free space and observed allocation contention. Keep tempdb data files equally sized with matching growth settings. Preallocate enough space for the usual workload, while allowing for unplanned growth.

A file-count warning is a starting point, not permission to add files blindly. Investigate the contention and the workload before changing the configuration. Keep the tempdb log distinct from advice about multiple data files.

4. Is the authentication choice intentional?

For SQL Server on Windows, Windows Authentication mode disables SQL Server Authentication. Mixed mode enables both, while Windows Authentication remains available. Check which mode the applications require and how their accounts are managed.

I prefer Windows authentication when possible. Mixed mode does not mean every application needs the sa login or administrative privileges. Review real dependencies before changing authentication or enabling a powerful account.

5. Has an integrity check completed successfully?

Review the last completed consistency check, its scope, output and follow-up actions. A scheduled job that failed or stopped early is not a clean result. Keep the output alongside the database identity and execution time.

DBCC CHECKDB checks database integrity; PHYSICAL_ONLY performs a narrower physical check. Periodic full checks are still worth running, at a pace that fits the environment. A reported corruption requires an evaluated recovery response, with restoration from a known good backup preferred over repair options.

6. Does the recovery model match the promise?

Simple recovery does not support transaction-log backups or point-in-time restores between database backups. That makes it unsuitable when the requirement depends on those capabilities. It is not automatically wrong for every database.

Full recovery requires a working log-backup strategy and an intact required backup sequence. Setting the model alone does not satisfy the recovery requirement. Check the actual backups and tested recovery procedure before promising how much work can be recovered.

Turn the review into a short action list

For each finding, record the affected database, evidence, consequence, owner and proposed action. Separate an urgent recovery gap from a configuration preference. A useful review ends with work someone can explain and verify.

Automated rules can help collect candidates, but their recommendations still need workload context. I would rather leave a justified exception than make a disruptive change only to clear a warning. Save the reason and a date for reviewing that exception again.

A short list you can explain beats a long list nobody reads.

A health check is not a warning list, it is a short list of justified actions.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.