Part of the DBA-Tools Project.
Most of the 130+ 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 39 scripts against the target instance, one per diagnostic area, real inventory, backups, waits, security, TempDB, indexes, jobs, and more, and saves each result 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\.-20260731-154147
--------------------------------------------
[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\monitoring\disk-space\Get-AutogrowthHistory.sql
[OK ] autogrowth-history
--------------------------------------------
OK: 39 | Failed: 0 | Skipped: 0
All 39 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.
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, and more, producing a flat, ranked findings list: CRITICAL, WARNING, INFO.
Real output, same collection:
============================================
DBA Health Check Review
============================================
[CRITICAL] Backup DemoDatabase
Full backup is 80 hours old (threshold: 24h)
[CRITICAL] DBCC CHECKDB migdb_37A45A06
CHECKDB overdue — 15 days since last integrity check (run immediately)
[CRITICAL] Security sa
Login risk flag: SA_ENABLED
[WARNING ] Maintenance Job DBA - Collect Deadlocks
Last run FAILED. Check SQL Agent job history for details.
[WARNING ] Security DBA_Junior
Login risk flag: PASSWORD_POLICY_OFF
...
--------------------------------------------
CRITICAL: 13 | WARNING: 39 | 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 55 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.
.\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 55 findings above, this project’s own lab instance, run for this post:
Verdict
This instance has 13 CRITICAL and 39 WARNING findings, but they collapse into four real root causes, not fifty-two unrelated problems. The single most important thing to fix today: two of the three generated backup jobs exist on the instance but sit disabled and have never run, which is why six real databases are carrying 80-hour-old full backups and 79-hour-old log backups on FULL recovery model. Enable those two jobs. Everything else here is either a known, already-diagnosed incident or a lab-environment configuration choice that would need a different verdict on a production server.
That’s the difference in one paragraph: the rules engine correctly flagged 13 separate CRITICAL rows for stale backups and overdue integrity checks across six databases. The assessment read the same evidence and found the actual cause was two disabled SQL Agent jobs, one fix, not thirteen. It went further and connected a three-day-old, already-documented memory-pressure incident to a second, less obvious consequence nobody had checked: the integrity check job that failed once during that incident was left disabled afterward, silently turning a one-time blip into 15 days with zero corruption detection running at all.
Priority Issues (excerpt)
2. A three-day-old memory-pressure incident silently disabled the integrity-check safety net
Evidence:DBA - Integrity Checkshowslast_run_status = Failed, dated 28/07/2026, andstatus = Disabled.dbcc-checkdb.csvconfirms the consequence: all fivemigdb_*databases haven’t had a successfulDBCC CHECKDBsince 16/07/2026, 15 days ago, well past the 7-day threshold.
Impact: This isn’t a script bug, it’s the same host-memory-pressure event already documented on this exact date. The real problem now is that the failure caused the job to be left disabled afterward, so corruption detection has silently not run at all for 15 days, a bigger exposure than the original memory blip.
Fix: Re-enableDBA - Integrity Check, run it once manually to confirm it succeeds, and treat “job disabled after a single failure” as its own watch-list pattern going forward.
The full report also separates a currently active problem (two collector jobs failing on every run right now, not historical) from a lab-appropriate, low-real-risk finding (broad sysadmin membership with weak password settings, which would be a production CRITICAL but is reasonable hygiene debt on an isolated dev box), exactly the “judge severity in context” instruction the rubric asks for, something a flat threshold rule structurally cannot do.
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 55 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.
Related Scripts
This workflow’s collection touches nearly every pillar on the site; a few of the most directly related:
- Get Backup Coverage, the detailed drill-down behind the Backup findings above
- Get Last DBCC CHECKDB, the standalone version of the integrity-check finding
- 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