01
Backups, Restores and CHECKDB
Last full, differential and log backup per database, recovery models, the log backup chain, backup checksums and encryption, one timed test restore, and the last successful CHECKDB per database.
SQL Server Health Check
SQL Server health check: backups, configuration, waits, security and patch level
Last reviewed by Tyler Eastridge
A SQL Server health check is a fixed-scope, read-only review of each SQL Server instance and its databases that ends in a written report ranked by production risk. AceMQ checks six things: whether your backups restore, how each instance is configured, what it waits on, who can do what, how it fails over, and where its version stands against Microsoft's lifecycle. It is delivered by AceMQ with partner SQL Server specialists on SQL Server 2016 to 2025, on Windows, Linux, Azure VMs and Azure SQL Managed Instance. Nothing on your instances changes during the review.
- areas reviewed on every instance
- 6
- SQL Server 2016 extended support ended, per Microsoft
- 14 Jul 2026
- to answer a quote request
- 1 hour
Is This You?
You probably need this if…
- 01
Backup jobs report success every night and nobody has restored one this year
- 02
Nobody can say when DBCC CHECKDB last passed on each database
- 03
The DBA who set the instance up has left and it still runs on the settings it was installed with
- 04
An auditor, insurer or customer questionnaire asks for evidence on backups, patching and access
- 05
SQL Server 2016 or 2017 is in production and nobody has settled between upgrade, Extended Security Updates and a move
- 06
SQL Server Audit is switched on and someone has taken that to mean the instance is healthy
- 07
Agent jobs fail and the alert goes to a mailbox nobody reads
Outcomes
Where you are now, and where you end up
Concrete state changes, not deliverable counts. This is what actually differs about your SQL Server estate when the engagement closes.
Before
Backup jobs are green, so recovery is assumed.
After
One chosen database restored to a scratch server, timed against your recovery target, with any gap in the log backup chain found from the backup history in msdb.
Before
Nobody knows when CHECKDB last passed.
After
The last successful CHECKDB time per database, read from the database itself, and a schedule that fits your backup window.
Before
Settings are whatever setup left behind.
After
Every value that differs from the default or from what the hardware needs, with the reason and the risk of changing it.
Before
The version question is a line in a slide deck.
After
Build, cumulative update level and Microsoft lifecycle date for each instance, and a stay, upgrade or move recommendation your change board can act on.
Scope
What's covered
- 02
Configuration and Storage
Max server memory, MAXDOP, cost threshold for parallelism, tempdb layout, file growth, free space on the data, log and tempdb volumes, compatibility levels, and database options such as auto-close, auto-shrink and page verification.
- 03
Waits, Query Store and Capacity
What the instance waits on since its last restart, blocking chains in the history, whether Query Store is on and sized, and the growth trend of data, log and tempdb files.
- 04
Security and Access
Who holds sysadmin, SQL logins without password policy, xp_cmdshell, linked servers with stored credentials, Agent proxies, orphaned users and whether SQL Server Audit is set up for what you need to prove.
- 05
Availability and Agent
Availability group, log shipping and failover cluster state, replica lag, SQL Server Agent job failures, and whether alerts for severe errors and corruption reach a person.
- 06
Patch Level and Lifecycle
Build and cumulative update per instance against Microsoft's current builds and lifecycle dates, and what stands between each instance and a supported release.
The Engagement
How it actually runs
Every phase has a defined duration and a concrete artifact handed over at the end of it. You always know what stage you're in and what you've received.
- Phase 1Scoped per estate
Scoping Call
We agree which instances are in scope, their versions and editions, where they run, and what your DBA can run. We also ask whether a scratch server exists for the restore test, and whether query text may leave your network, since Query Store and the plan cache can hold literal values.
You receive
- Instance list and focus areas
- Read-only diagnostic scripts for your DBA to review
- Agreement on how query text is handled
- Phase 2Scoped per estate
Read-Only Capture
Your DBA runs the agreed diagnostics, or runs them with us over screen share. They read catalog views, dynamic management views, msdb history and the error log. They do not read table rows, and no standing access to your instances is needed.
You receive
- Diagnostic output files returned to AceMQ
- List of anything we could not read and why
- Phase 3Scoped per estate
Review and Restore Test
A senior AceMQ engineer and, where the estate needs it, a partner SQL Server specialist work through the six areas. One agreed database is restored from its backups on a scratch server and the restore is timed against your recovery target.
You receive
- Evidence per finding
- Restore test result with timings
- Notes on anything that blocks an upgrade
- Phase 4At the end of the review
Report and Walkthrough
A written report ranked by production risk. Each finding names its evidence, the change we recommend, the effort and what could go wrong with it, and a walkthrough with your DBAs follows. Remediation is scoped separately, so the report is useful whoever makes the changes.
You receive
- Written report
- Ranked finding list with a change per item
- Walkthrough with your DBAs
What a SQL Server health check reads, and where each answer comes from
Every finding should trace to something your DBAs can open. SQL Server keeps most of what a review needs in its own catalog views, dynamic management views and msdb history, so the review reads metadata and statistics, not your business data.
| Area | Evidence we read | A finding looks like |
|---|---|---|
| Backups and restores | msdb backup and restore history, the Agent job output, one test restore | Full backups are current, but log backups stopped weeks ago on a database in the full recovery model, so the log keeps growing and point-in-time recovery is gone. |
| Integrity | DATABASEPROPERTYEX LastGoodCheckDbTime, job history, each database's page verify option | A database that has never had a successful CHECKDB, or one still on an older page verify setting after an in-place upgrade. |
| Configuration | sys.configurations, sys.databases, tempdb and data file layout | Max server memory left at its default, so the instance competes with the operating system. |
| Waits and workload | sys.dm_os_wait_stats, Query Store views, file growth history | Most of the wait time is one wait type, and the queries behind it are few. |
| Security | sys.server_principals, role membership, sys.sql_logins, sys.servers, Agent proxies | A shared application login in sysadmin, or a linked server that connects with a stored administrator password. |
| Availability and Agent | Availability group state DMVs, msdb job history, configured alerts | A secondary replica is behind and the Agent alert for it is disabled. |
| Patch level | SERVERPROPERTY build and update level, Microsoft's lifecycle page | An instance several cumulative updates behind on a release that Microsoft supports. |
About query text
Query Store and the plan cache store statement text, and statements can contain literal values. We agree with you before the capture whether that text is included, masked or left out, and the waits and configuration review does not depend on it.
Backups: a successful job is not a successful restore
A backup job that finishes proves the job ran. It does not prove that the file restores, that the chain of log backups is unbroken, or that restoring takes less time than you can be down. The review tests each of those separately.
- VERIFYONLY is a start, not a restore — Microsoft's documentation says RESTORE VERIFYONLY checks that the backup set is complete and readable, but does not try to verify the structure of the data inside it. A backup taken with checksums gives it more to check. We treat a verify as a lower bar than a restore.
- The log chain — From the backup history in msdb we look for databases in the full recovery model with no log backups, and for gaps in the chain that would end a point-in-time restore early.
- One real restore, timed — We restore one agreed database to a scratch server, run CHECKDB on the copy, and set the elapsed time beside the recovery time your business has said it can accept. That number, not the backup job status, is what the recovery target depends on.
- Where the backups live — A backup on the same disk or the same host as the database does not survive the failure it is meant for. We record where copies go and who can delete them.
- Integrity checks — DATABASEPROPERTYEX returns the time of the last successful CHECKDB, and 1900-01-01 if it has never run. The property applies from SQL Server 2016 SP2 and 2017 CU9, so on older builds we read job history instead.
Configuration: settings we compare with the defaults
Most configuration findings are settings nobody chose. The review compares each value with the documented default and with what the hardware and workload need. These are the ones that most often matter.
| Setting | Default | What we look for |
|---|---|---|
| max server memory (MB) | 2,147,483,647 MB, which sets no limit | Microsoft recommends an upper limit in every version and setup has offered a value since SQL Server 2019. An instance with no limit can starve the operating system or a second instance. |
| MAXDOP and cost threshold for parallelism | 0 and 5 in sp_configure; since SQL Server 2019, setup recommends a MAXDOP value from the processor count | Values that predate the current hardware. Microsoft calls 0 not the recommended MAXDOP for most cases, and calls the threshold of 5 a starting point, not a recommendation. |
| tempdb data files | Setup adds one per logical processor, up to eight, from SQL Server 2016 | Files of unequal size or growth, which skews the proportional fill, and a single file on an instance upgraded or built by hand. |
| PAGE_VERIFY | CHECKSUM on new installations | An older option kept on a database that came through an in-place upgrade or a restore. |
| AUTO_CLOSE and AUTO_SHRINK | Off | Either switched on, which costs performance and fragments files. |
| Database compatibility level | That of the installed version for new databases | A restored database left at an old level after an upgrade, so new optimizer behaviour is not in use or has not been tested. |
| Query Store | Off on SQL Server 2016 to 2019, on for new databases from SQL Server 2022 | Off where the workload needs it, or on with a size cap that has filled and switched it to read-only. |
| File growth | Set per database at creation | Percentage growth on large files, and many small growth events that show up as pauses. |
Health check, SQL Server Audit, performance tuning and managed services: which one answers what
The words overlap in searches, and the work does not. SQL Server Audit is a feature that records events. A health check is a review that reads state and writes down what is wrong. The table separates the four, and the blog on the audit feature explains the first in more depth.
| Activity | Question it answers | What you get | Where it fits |
|---|---|---|---|
| SQL Server Audit (the feature) | Who did what, and when? | Events written to a file, the Windows Security log or the Windows Application log | Compliance evidence about activity. It says nothing about whether backups restore or settings are sound. |
| Health check | Is this estate in a state we can trust? | A written report ranked by production risk, with evidence | The first engagement, or before an upgrade, audit or contract renewal. |
| Performance tuning | Why is this workload slow? | A baseline and measured changes to queries, indexes and waits | After the health check names a hot spot. Covered under SQL Server consulting. |
| Managed services | Who runs this every day? | Scheduled DBA work and on-call cover, on your infrastructure or ours | When the report shows the work is continuous. Tiers 3 and 4. |
Where the version stands, and what the report recommends
Microsoft's lifecycle pages say SQL Server 2016 extended support ended on 14 July 2026 and SQL Server 2017 extended support ends on 12 October 2027. An instance past the end of extended support gets security fixes only through Microsoft's Extended Security Updates, which you buy from Microsoft. AceMQ gives no Microsoft licensing advice and does not sell ESUs.
The report puts each instance in one of three positions: supported with room to spare, supported but within a planning horizon, or out of extended support. For the second and third it lays out the engineering side of the choice: an in-place or side-by-side upgrade, what the application leans on that blocks one, and whether a move to Azure SQL or another engine is worth scoping. The cost of ESUs, and whether to buy them, stays between you and Microsoft.
Who this is not for
- A database down tonight — That is an incident. Use SQL Server support for an emergency response, and book the health check afterwards.
- Performance tuning of one query — A health check finds where to look. Tuning a workload is a consulting project.
- Licence or ESU advice — We do not advise on Microsoft licensing, true-ups or Extended Security Updates purchasing.
- A promised outcome — We do not quote a percentage by which a workload will speed up or a risk will fall before we have seen the diagnostics.
- Azure SQL Database single databases — Operating-system and instance-level checks do not apply. Scope these case by case. On Azure SQL Managed Instance the report states which checks do not apply.
Typical engagements
How these engagements run
Typical engagement scenarios: what the work covers and what it delivers. They describe the service, not past results.
Real-Time Manufacturing Data Ingestion Modernization
Global Automotive Manufacturer
Replacing fragile SQL-trigger-based ingestion with a reliable event-driven architecture for plant-floor data movement and low-latency operations.
RabbitMQMQTT
KafkaSQL Server
Kubernetes
SQL Server to PostgreSQL: Babelfish Assessment and Migration Path
SaaS Platform Team
A typical engagement for a team on SQL Server 2016 whose applications are written in T-SQL: assess Babelfish compatibility, choose between a bridge and full conversion, and plan the cutover.
PostgreSQLSQL ServerBabelfishAWS DMS
Pentaho Lineage and Documentation Recovery Assessment
Utility Holding Company
Reconstructing lineage and documentation for an undocumented estate of legacy Kettle transformations before anyone attempts to change or replace them.
PentahoPostgreSQLSQL Server
dbtPentaho Job Failure and Data Integrity Support
Logistics and Distribution Company
Ongoing support for a Pentaho Data Integration estate that must keep running reliably while a longer-term replacement is planned.
PentahoSQL ServerPostgreSQL
Docker
Debezium MySQL Binlog Retention and Schema History Support
Online Travel Platform
Ongoing support for a MySQL change data capture pipeline that needed a full re-snapshot after a planned outage outlasted binlog retention and a trimmed schema history topic.
Debezium
MySQLSQL ServerOracle DatabaseKafka
Amazon RDSSQL Server Always On Availability Group Failover Review
Regional Logistics Operator
A typical engagement: reviewing an Always On availability group that spans two sites, so the first real failover is not the first test of its replica modes, quorum and lag tracking.
SQL ServerWindows Server Failover ClusteringAlways On Availability GroupsSQL Server 2016 Upgrade and Extended Security Updates Planning
Manufacturing Group
A typical engagement: inventory a mixed SQL Server 2016 estate after extended support ended, then decide per instance between upgrading, Extended Security Updates and Azure SQL Managed Instance.
SQL ServerAzure ArcQuery StoreManaged SQL Server in the Customer's Own Cloud Account
SaaS Platform Team
A typical engagement: AceMQ takes over operations of a SaaS platform's SQL Server estate in the customer's own AWS account, with the data and the licences staying with the customer.
SQL ServerAmazon EC2Always On Availability Groups
FAQ
Questions about SQL Server health check
What is a SQL Server health check, and what does it include?
A fixed-scope, read-only review of a SQL Server instance that ends in a written report. It covers six areas: backups, restores and CHECKDB; configuration and storage; waits, Query Store and capacity; security and access; availability groups and Agent jobs; and patch level against Microsoft's lifecycle. The output is a written report ranked by production risk, with the evidence for each finding.
Is a SQL Server audit the same as a health check?
No. SQL Server Audit is a feature that records events, such as logins and permission changes, to a file or a Windows log. A health check reviews the state of the instance: whether backups restore, how it is configured and how it fails over. An audit trail can be one input to the security section.
Can you do it without access to production data?
Yes. The diagnostics read metadata, statistics and history, not table rows. Your DBA runs them or runs them with us over screen share, and we do not need standing access. Query text can contain literal values, so we agree beforehand whether it is included, masked or left out.
Does a SQL Server health check change anything or need downtime?
No. The capture is read-only and runs against the live instance, so no downtime is needed. The one exception is the restore test, which runs on a scratch server and not on the production instance. Remediation is scoped separately. Run it once to set a baseline, again before an upgrade or an audit, and on a schedule under proactive monitoring if you want the checks repeated.
Which SQL Server versions and platforms do you cover?
SQL Server 2016 to 2025 on Windows and Linux, on-premises or on cloud virtual machines, and Azure SQL Managed Instance. The report says which checks do not apply on a managed platform.
Let's talk
Tell Us Which SQL Server Instances You Run
Send the versions, the number of instances and where they run. We scope a health check and reply to your quote request within 1 hour.
Get in Touch
Talk to a SQL Server Expert
Send us a message and we'll follow up within 1 hour — or book a free 30-min consultation directly.
305-204-2607info@acemq.com66 W. Flagler St. 9th Floor
Miami, FL 33130
Miami, FL 33130
Prefer to talk now? Call us directly or use the consultation tab to find a time that works.