Most of the 180+ scripts on this site answer one specific question, run when you already know what you’re looking for. This post is different. It’s the workflow for the moment you don’t know what to look for yet, an inherited server, an on-call page, a new instance nobody’s documented, and you need a complete picture fast: what’s actually wrong, ranked by how much it matters, not just a wall of green checkmarks and a few numbers you have to interpret yourself.
Three steps. Collect, review, assess. The first two are deterministic scripts. The third is where this repo’s actual point is: an AI reading the same raw evidence a senior DBA would, correlating what the rules engine can only flag in isolation, and writing the kind of judgment call a checklist can’t make.
Step 1: Collect
git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1
.\powershell\reporting\Invoke-HealthCheckCollection.ps1 -ServerInstance .
This runs 45 scripts against the target instance, one per diagnostic area, real inventory, backups, waits, security, TempDB, indexes, jobs, and the full High Availability set: Always On replica state, failover readiness and latency, mirroring endpoints, replication, and FCI node blips. Each result is saved as a named CSV in a timestamped folder under output-files\healthcheck\. Nothing here is destructive; every script behind this collection is read-only.
Real run against this project’s own lab instance, PWSQL01:
============================================
DBA Health Check Collection
============================================
Server : .
Output : output-files\healthcheck\.-20260813-172805
--------------------------------------------
[repo-sql] Script : sql\inventory\Get-VersionAndEdition.sql
[OK ] server-info
[repo-sql] Script : sql\inventory\Get-OsAndHardwareInfo.sql
[OK ] os-hardware
...
[repo-sql] Script : sql\high-availability\replication\Get-ReplicationStatus.sql
[OK ] replication-status
[repo-sql] Script : sql\high-availability\fci\Get-LastNodeBlip.sql
[OK ] last-node-blip
--------------------------------------------
OK: 45 | Failed: 0 | Skipped: 0
All 45 ran clean. A [No rows returned] note against a handful (suspect pages, missing indexes, linked servers) isn’t a failure, it means the check ran and genuinely found nothing, which is itself useful information the next step will use. The High Availability scripts go one step further: on an instance with no AGs, mirroring, or replication configured they return a status row saying exactly that, so the assessment can report those areas as checked and clean instead of silently skipped.
Step 2: Review
.\powershell\reporting\Review-HealthCheckOutput.ps1
This reads every CSV from the most recent collection and applies a fixed set of threshold rules, stale backups, overdue DBCC CHECKDB, disabled or failing maintenance jobs, weak login settings, high I/O latency, databases at their growth limits, volumes low on free space, expiring certificates, orphaned users, known error-log patterns like corruption errors and memory pressure, and unhealthy AG replicas or endpoints, producing a flat, ranked findings list: CRITICAL, WARNING, INFO.
Real output, same collection:
============================================
DBA Health Check Review
============================================
[CRITICAL] Backup DemoDatabase
Full backup is 394 hours old (threshold: 24h)
[CRITICAL] DBCC CHECKDB DBAMonitor
No CHECKDB ever recorded — integrity of this database is unknown
[CRITICAL] Security sa
Login risk flag: SA_ENABLED
[WARNING ] Disk Space C:\
Volume has 5% free (5.30 GB of 106.22 GB)
[WARNING ] Error Log Memory pressure
11 entry/entries reporting working set paged out or insufficient
buffer pool memory — check max server memory vs host RAM
[WARNING ] Wait Statistics PAGEIOLATCH_SH
46.4% of total wait time — Data file read I/O bottleneck
...
--------------------------------------------
CRITICAL: 15 | WARNING: 45 | INFO: 3
This is genuinely useful on its own, it’s exactly what a rules-based health check tool gives you: a real, honest list, findings.csv written alongside the raw data for tracking over time. But 63 flat findings with no ranking beyond CRITICAL/WARNING/INFO still leaves the actual DBA work undone: which of these are really one problem wearing several hats, and which one do you fix first.
Step 3: AI Assessment
This is the step that makes the other two worth automating. The same CSVs, plus findings.csv, get handed to an AI following a fixed rubric (powershell/reporting/ai-assessment-rubric.md) whose job is explicitly what the rules engine can’t do: correlate signals across files, find root causes, judge severity in context, and write a prioritized report a DBA can act on directly.
The AI step is optional by design. Steps 1 and 2 are plain PowerShell and T-SQL: the collection and the deterministic rules review need no API key and no internet access, and still hand you the ranked CRITICAL / WARNING / INFO findings list. In a locked-down environment, stop after step 2 and the workflow stands on its own; the AI pass adds the cross-file correlation on top.
.\powershell\reporting\Invoke-AiAssessment.ps1
(Needs ANTHROPIC_API_KEY set; -DryRun previews the prompt without calling the API. Working inside Claude Code itself, the assessment step happens directly, reading the same CSVs and following the same rubric, no API call needed.)
Real assessment from the same 63 findings above, this project’s own lab instance, run for this post:
Verdict
This instance is currently starved of memory to the point where the host is paging SQL Server out, the plan cache has been flushed empty, and the monitoring collectors themselves are failing with out-of-memory errors. Nothing here is production-facing, so nothing needs action today in an operational sense, but the single most important fix is the memory squeeze (a 1,600 MB cap on a 7.78 GB host that is overcommitted), because it is quietly breaking every other capability on the box: collectors, Agent jobs, and any meaningful performance testing. Second priority: SQL Server Agent is not running, so no scheduled collection or maintenance is happening at all.
That’s the difference in one paragraph: the rules engine correctly produced separate findings for seven failing Agent jobs, a memory-pressure pattern in the error log, an I/O-heavy wait profile, and a plan cache full of single-use plans. The assessment read the same evidence and found they are all one problem. The buffer pool is too small for the host it’s on, so Windows pages SQL Server out, the plan cache gets flushed (which is why top-cpu-queries.csv came back empty, nothing survives in the cache long enough to be measured), collector steps die with error 802, and the top waits are exactly what a starved buffer pool produces.
Priority Issues (excerpt)
1. Memory starvation is degrading the whole instance Evidence: max server memory is capped at 1,600 MB against 7.78 GB physical; the error log holds 11 entries in 24 hours of “a significant part of SQL Server process memory has been paged out”; collector job steps are failing with error 802 (insufficient memory in the buffer pool);
dm_exec_query_statsis empty (0 rows, 6 cached plans); top waits are PAGEIOLATCH_SH at 46.4% and RESOURCE_SEMAPHORE at 21.7% with 8.8-second average waits. Impact: collectors fail, query performance data is unusable, and any test run on this box measures paging, not SQL Server. Fix: decide the box’s memory budget. If SQL matters here, raise max server memory to 3 or 4 GB and set min server memory to match. Otherwise accept the squeeze and stop the collectors.
The full report also caught a subtler split inside those seven failing jobs: six were dying from the memory pressure, but the deadlock collector had never succeeded, for a completely different reason, the classic Agent gotcha where T-SQL job steps run with QUOTED_IDENTIFIER OFF and the step uses XML methods, so it failed with error 1934 on every single run. Same “Last run FAILED” column in the job history, two unrelated root causes, exactly the split a flat threshold rule structurally cannot make.
Why Three Steps, Not One
- Collection and review can run unattended, on a schedule, before anyone’s even looking. A daily collect + review job means the findings are already waiting, not generated live while someone’s already stressed about an incident.
- Review is deterministic and fast, the same 63 findings come back every time from the same data, useful for tracking whether a specific issue count is trending up or down over weeks.
- Assessment is where judgment happens, and judgment is expensive to run and genuinely different every time. Running it on demand, when a human is actually going to read and act on the result, is the right cadence, not every 15 minutes alongside the collectors.
Interpreting the Assessment, Not Just Reading It
- Read the Verdict first, always. It’s one paragraph specifically written to answer “do I need to act today,” everything below it is supporting detail.
- Priority Issues are pre-correlated. If the assessment groups several rules-engine CRITICAL rows into one numbered issue, that grouping is itself the finding, don’t re-split them back into separate tickets without a reason.
- Watch List items are not yet problems. They’re the assessment explicitly telling you what threshold would turn them into one, worth a second look on the next run, not immediate action today.
- Checked and Clean matters as much as the findings. It’s the difference between “nothing was found” and “this specific thing was checked and found fine,” don’t assume silence on an area means it wasn’t looked at.
Adjusting the Thresholds
The review script’s thresholds assume typical daily-backup schedules and standard SLA expectations. Open powershell\reporting\Review-HealthCheckOutput.ps1 and adjust the relevant check block directly, full-backup age, log-backup age, I/O latency, DBCC CHECKDB staleness, all are plain, editable conditionals, not a config file to learn.
Running Against a Remote Server
.\powershell\reporting\Invoke-HealthCheckCollection.ps1 -ServerInstance PROD01\SQL2019
.\powershell\reporting\Review-HealthCheckOutput.ps1
.\powershell\reporting\Invoke-AiAssessment.ps1
Each server’s collection lands in its own timestamped folder, so multiple instances can be collected and reviewed independently without collision.
This workflow has dba-tools gather the evidence and an AI correlate it. The sqldba MCP server is the same idea pointed the other way: the assistant asks this site, rather than answering from memory, whenever it needs an error, a wait type, a patch level or a script.
Microsoft Learn: DBCC CHECKDB
Related Scripts
This workflow’s collection touches nearly every pillar on the site; a few of the most directly related:
- SET Options Have Incorrect Settings (Error 1934), the Agent QUOTED_IDENTIFIER gotcha described above, worked through in full
- Get Backup Coverage, the detailed drill-down behind the Backup findings above
- Get Last DBCC CHECKDB, the standalone version of the integrity-check finding
- Cumulative Wait Stats Lie: Measuring Waits Over an Interval
- Collectors and Baseline Infrastructure (pillar), the standing jobs behind several of the findings this workflow surfaces
- Backups and Recovery (pillar)
- Security (pillar)
- DBA Scripts: The Complete Guide, the full pillar index
Summary
Collection and review turn “is this server okay” into a real, ranked list in under a minute, no guessing, no manual DMV queries. Assessment turns that list into the thing a rules engine structurally can’t produce: root causes correlated across files, severity judged against the actual context of the instance, and a specific next action for each priority issue. Run collection and review on a schedule; run the assessment when someone’s actually going to read it and act.
Leave a Reply