The on-call DBA that never logs in

47 minutes ago

15 min read

A headless Claude Code run that answers a disaster alert with a measured root cause in under five minutes, can only read, and says what it couldn't check. Ten rules, with the config, and a real failover.

I am a lazy DBA, and I want to automate repetitive tasks so that I can focus more on building what is more valuable.

The most repetitive thing I do is the first half hour of an incident. An alert fires, and before any thinking happens I get the laptop out, connect the VPN, RDP, open SSMS and run the same first queries as last time: which node is primary, is the availability group healthy, did anything restart, are there new dumps, is SQL Agent running, blah blah blah.. When the alert says DISASTER, that warm-up costs the most, and it tends to arrive at the worst hour.

So I handed the warm-up, and the first root-cause analysis, to an agent. On 6 November at 07:51 in the morning, a CRITICAL from our own alert jobs said the R3 availability group had changed role, and 32 seconds later the infrastructure monitor posted a DISASTER: SQL Agent on r3-db1 was down. I did not have to prepare a laptop or a connection. At 07:55:47 the RCA was already in my direct messages: r3-db1 had crashed, r3-db2 was the primary, no data loss, and because the crashed node's Agent was still down, the log backups had stopped, with the two commands to fix it. Four more DMs followed within 24 seconds, one for each alert. At 07:59 I pasted the first one into the incident thread, and eleven seconds later one of our engineering leads replied: "Great report by AI agent at least 👏". When I re-measured the key numbers afterwards, they held.

The DMs came from a tools server, not from a person. A listener starts one headless Claude Code run 30 seconds after an alert lands in either of two channels, and a Scheduled Task starts one every five minutes anyway. Each run reads the new posts, measures the nodes, and either sends one message per topic or stays quiet. This post is how it is built, the ten rules that made it safe enough to leave running, and what it did on that morning. Three sections are interactive: click through them.

Where it comes from. The SQL side of this monitor is the read-only MCP server from my earlier post, A Read-Only SQL MCP, Powered by dbatools. That post was about a human asking questions. This one takes the human out of the loop, so the server had to grow: a dedicated least-privilege login instead of the caller's own, a fan-out query across the fleet, a certificate-signed procedure for crash-dump text, TLS verified against our own CA bundle instead of "trust the server", and tools built on the diagnostic surfaces Microsoft itself recommends: the DMVs, Query Store, Extended Events, system_health, the default trace, msdb job history, plus dbatools' Get-/Test- cmdlets for the host. The read-only guard is unchanged: SELECT, WITH and DECLARE pass; everything else is rejected before it reaches SQL Server. The next section is what that server integrates and how it is locked down; the rules after it are the monitor built on top.

What the SQL side is built on: Microsoft's own diagnostic surfaces

I did not invent a diagnostic method for the model. I gave it the same surfaces Microsoft documents for humans, one tool per question, so every answer is a documented DMV, Query Store view or Extended Events read that any DBA can re-run in SSMS. The server has 55 tools in the DBA profile; the monitor uses about a dozen of them:

"What is running, who is blocking whom?" get_active_sessions, get_blocking_chains, get_long_running_transactions → sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_sql_text, sys.dm_tran_*. The post-incident version, get_hiq, reads a minute-by-minute sp_WhoIsActive capture table so "what was running at 02:32" still has an answer a day later.
"Did a plan change?" get_query_store_regressions, get_top_queries → sys.query_store_query, query_store_plan, query_store_runtime_stats, with execution_type_desc = 'Aborted' read as the application's command timeouts and sys.database_query_store_options checked first so a zero is never mistaken for "none".
"What is it waiting on?" get_wait_stats, get_latch_stats, get_file_io_stats, get_cpu_history → sys.dm_os_wait_stats, sys.dm_io_virtual_file_stats, sys.dm_os_schedulers, the system_health session's scheduler-monitor ring buffer, sys.dm_os_performance_counters.
"Is the AG healthy, and which node is primary right now?" get_ag_health, get_current_primary → sys.dm_hadr_database_replica_states, sys.dm_hadr_availability_replica_states, sys.availability_group_listeners, including the failure_condition_level the test alert lied about. A <region>-primary alias resolves to the current primary at call time, so a failover changes nothing in the prompt.
"Did it crash?" get_memory_dumps, read_sql_dump, read_error_log → sys.dm_server_memory_dumps, the on-disk SQLDump*.txt through the signed procedure, xp_readerrorlog, and sys.dm_os_loaded_modules to see which DLLs are inside sqlservr.exe right now.
"Who did it?" the deadlock graph and the RCA Extended Events session → sys.fn_xe_file_target_read_file on system_health and on our own session; login failures → the default trace through sys.fn_trace_gettable; jobs → msdb.dbo.sysjobhistory, sysjobsteps.
"Is the whole instance in good shape?" run_health_check → 66 read-only checks in seven categories, built on dbatools' Test- and Get- cmdlets that encode Microsoft's published guidance: Test-DbaMaxDop, Test-DbaMaxMemory, Test-DbaPowerPlan, Test-DbaOptimizeForAdHoc, Test-DbaBuild (is this CU still supported), Test-DbaDbQueryStore, Test-DbaDbCompatibility, Test-DbaIdentityUsage, Measure-DbaDbVirtualLogFile, Get-DbaLastGoodCheckDb, Test-DbaConnectionAuthScheme, plus DMV checks for instant file initialization and Lock Pages in Memory (sys.dm_server_services, sys.dm_os_sys_info), tempdb layout, PAGE_VERIFY CHECKSUM, backup age per database on the replica that sys.fn_hadr_backup_is_preferred_replica() says should take it, and sp_BlitzIndex for index health.
"Compare it with a node that is fine." fan_out_query runs one SELECT on every instance in a list. It is the single most useful tool in the set, and the fleet sweep in Rule 8 is one call to it.

How that server is secured, since the monitor inherits all of it:

Three independent read-only layers: the login's own grants; ApplicationIntent=ReadOnly so reads land on a readable secondary and a write fails at the engine; and the query allowlist, which accepts SELECT/WITH/DECLARE and Get-/Test-/Measure-/Find-Dba* only, and rejects DML, DDL, DCL, EXEC, BACKUP, RESTORE, DBCC, KILL, WAITFOR, dynamic SQL, batched statements, and read-verb cmdlets that secretly write.
Encrypt=True with the certificate verified against our own CA bundle, never TrustServerCertificate. The fleet's certificates were re-issued with proper SANs so strict TLS validates by name.
No secret on disk: the fleet file carries a placeholder, a literal password is refused at startup, and the real value comes from the OS credential store (SQL_CRED_TARGET) or the environment.
Per-user identity by default: a human runs under their own login. The monitor is the exception, and it got a dedicated least-privilege login (Rule 4) rather than borrowing mine.
Every result is row-capped (500 for a query, 200 for most tools) with a visible RESULT CUT banner, and a guardrail blocks unbounded scans of the large message-log tables that carry PII.
256 automated tests pin each profile's exact tool set and every read-only guard, so a pull request that widens the surface fails before it ships.

The shape of it

One tick is one model session. Click Next to run one.

Widget 1: one tick, step by step. Next, Back, Replay; click a step card to jump to it; arrow keys work when the widget has focus.

Everything that follows is about keeping that loop boring: the model never holds a secret, never writes to SQL, never starts a channel message, and never says more than it measured.

Rule 1. Make it a tick, not a daemon

A long-running agent accumulates state, context and surprises. A tick starts clean, does one job, and exits. Ours is a Scheduled Task every five minutes, plus a listener that starts the same task on demand, running a PowerShell wrapper around claude -p:

# the runner, in short
$psi = [System.Diagnostics.ProcessStartInfo]::new()
$psi.FileName = $claudeExe                       # a real .exe, not the npm shim
$claudeArgs = @('-p', '--mcp-config', $mcpCfg, '--max-turns', '100',
                '--output-format', 'json', '--allowedTools') + $allowed
foreach ($a in $claudeArgs) { $psi.ArgumentList.Add($a) }   # per-argument quoting by .NET
$psi.RedirectStandardInput = $true
$proc.StandardInput.Write($promptText); $proc.StandardInput.Close()   # the prompt goes in on stdin
if (-not $proc.WaitForExit(900 * 1000)) { $proc.Kill($true); throw 'tick timed out' }

Three details that saved me a day each:

The prompt goes in on stdin. It is thousands of characters; no command line should carry it.
A lock file stops overlapping ticks. If the previous one is still running (under 17 minutes, longer than the 15-minute timeout), skip and log.
One log line per tick, always the same shape: ok result=quiet|dm_sent cost_usd=0.21. Select-String 'cost_usd' over the log folder is your bill.

Costs, measured: about USD 0.22 for a quiet tick, up to 0.85 with a diagnosis; at a 5-minute cadence the budget is 65 to 75 USD a day. The turn and time budgets went up from 40 turns and 9 minutes when the disaster channel joined, because every disaster alert now gets a full playbook of up to 35 tool calls.

The only long-running piece, the Slack listener added the day after go-live (Rule 7), never reasons: it only starts ticks.

Rule 2. Allowlist exact command prefixes, and expand the paths yourself

Claude Code's --allowedTools takes rules of the form Tool(prefix:*). Ours:

$allowed = @(
  'mcp__sql', 'mcp__sql__*',                 # the read-only MCP tools
  'Read', 'Write', 'Edit', 'Glob', 'Grep',   # the state file and DM bodies
  "Bash(pwsh -NoProfile -File `"$slackFwd`":*)",   # exactly two scripts, by full path
  "Bash(python -I `"$capFwd`":*)"
)

Two things bit me. First, a bare is not a wildcard; : is. My first live tick had the Slack script "blocked by the permission layer" because of that one character. Second, the model has to type the command exactly as the rule reads. If your prompt says %HOME%/Slack-Api.ps1, the model will type $env:HOME/... and never match. So the runner substitutes every placeholder into the prompt text before launch, with forward slashes, and the model sees literal paths:

$promptText = $promptText.Replace('%ALERTMON_HOME%', ($InstallDir -replace '\\', '/'))
                         .Replace('%ALERTMON_DM_USER%', $DmUser)

Anything outside the list is simply not available to the headless run. There is no "bypass permissions" flag anywhere in this design.

Rule 3. Secrets go from the OS credential store into the environment, never on a command line

The API key, the SQL password and the Slack token live in Windows Credential Manager under the account the task runs as. The runner reads them and sets environment variables; the MCP server reads its password through SQL_CRED_TARGET; the Slack script reads its token the same way. No secret appears in the task definition, the prompt, a log line or a command line. The task runs with S4U, so there is no stored password for the account either.

If you remember one thing from this rule: the model should never see a secret, because anything it sees can end up in a message.

Rule 4. One login that can only read, and a server that refuses anything else

The MCP server is the one from the earlier post, and its guard still only passes SELECT, WITH and DECLARE. For the monitor I added a dedicated login instead of a person's:

-- svc_alertmonitor: one SID fleet-wide, so a failover changes nothing
GRANT VIEW SERVER STATE TO svc_alertmonitor;                 -- DMVs, Query Store, AG health
GRANT SELECT ON msdb.dbo.sysjobhistory TO svc_alertmonitor; -- and the job, backup and suspect_pages tables
GRANT EXECUTE ON xp_readerrorlog TO svc_alertmonitor;
-- nothing on application tables; no bulk rights
DENY EXECUTE ON msdb.dbo.sp_start_job TO svc_alertmonitor;
DENY EXECUTE ON msdb.dbo.sp_update_job TO svc_alertmonitor;
DENY EXECUTE ON msdb.dbo.sp_send_dbmail TO svc_alertmonitor;
DENY EXECUTE ON xp_cmdshell TO svc_alertmonitor;

Crash-dump text needs file access the login must not have, so it goes through a certificate-signed procedure in master that reads SQLDump*.txt and nothing else. The connection string carries ApplicationIntent=ReadOnly and Encrypt=True, and the server verifies the certificate against our own CA bundle. On the first install the bundle folder was missing from the package and every node failed verification with "unable to get local issuer certificate". The fix was to ship the bundle, not to set TrustServerCertificate=true. The monitor's first DM reported that outage itself, as "this channel was not covered from 07:55Z to 08:42Z".

The runner, the MCP guard and the login are three independent walls. A write would have to get through all three.

Rule 5. Give it exactly three ways to reach a person, and enforce them in code

The monitor can DM me, DM me plus two engineering leads (one IM each), or reply under a CRITICAL/HIGH alert in #db-alerts. It cannot start a channel message, and it cannot reply in #infra-alerts at all: that channel carries every team's alerts, so its triage comes to me as a DM. That rule lives in the Slack script, not in the prompt:

# dm: each target must be a user or an existing IM
foreach ($u in $targets) {
  if ($u -notmatch '^[UD][A-Z0-9]+$') { throw "Refusing: dm target '$u' is not a user or IM id." }
}
# reply: only with a thread_ts, only in #db-alerts
if (-not $ThreadTs -or $Channel -notin $ReplyChannels) { throw 'Refusing: reply must be threaded, in an allowed channel.' }

The bot has never posted a top-level message in either channel, and cannot, whatever the prompt is talked into. On 6 November the DM in the incident thread was pasted there by me.

Rule 6. Keep the memory in a state file: read first, write last

One tick is one model session, so everything it must remember is in one JSON file:

{
  "channels": { "C_ALERTS": { "name": "#db-alerts", "last_processed_ts": "1793922741.934419" },
                "C_INFRA":  { "name": "#infra-alerts", "last_processed_ts": "1793922693.806619" } },
  "known_open_issues": [
    "R3 login failures 17806/18452 from one client host = my own workstation running dbatools with Windows auth. WATCH: count only; DM only if a different host fails the same way or more than 20 in an hour."
  ],
  "repeat_counts": {},
  "replied_threads": { "1793690513.145849": { "severity": "CRITICAL", "replied_utc": "2026-11-03T12:55:29Z" } },
  "fleet_sweep": { "r1-db2": { "start_time": "2026-09-10T02:11:40Z", "agent_status": "Running", "dmv_dump_count": 0 } },
  "coverage_gaps": [],
  "updated_utc": "2026-11-03T13:15:02Z"
}

The watermark means it never re-reads a message. Every known issue carries a WATCH rule written in plain English, and a new message that matches one is counted, not re-investigated. replied_threads stops a second reply under the same alert. And the first thing a tick does is check updated_utc: if it is more than 20 minutes old, it writes a coverage gap, so a silent monitor leaves a mark.

One trap: always put a Slack ts in single quotes on a PowerShell command line. Unquoted, 1793690513.145849 becomes a double and loses its last digit, which shifts your watermark or targets the wrong thread. The script now rejects a malformed ts outright.

Rule 7. Route by the alert's own word, and let most ticks end in silence

Severity is not something the model decides. It is the first of CRITICAL, HIGH or anything else found in the alert text, with two overrides: a DISASTER from the infrastructure monitor and a production-down condition are CRITICAL whatever the label says. CRITICAL and HIGH in #db-alerts go to three people and into the alert's thread; everything else, and every #infra-alerts triage, comes to me alone. Repeats, weekly reports, OK/RESOLVED pairs, other teams' alerts and my own messages produce nothing.

#infra-alerts carries every team's alerts, so the monitor reads exactly three there: SQL Agent down, SQL connection error and the DB call-failure alert. The Slack script drops everything else before the model sees it, and each of the three gets its own playbook, never a "count only", because a disaster alert is exactly the case where I want the full work done before I look.

Click through the eleven cases, then try to break it.

Widget 2: who hears what, and seven ways to try to break it. Click any attempt, or Next to run them in order.

On go-live day the lag was the schedule plus the diagnosis: up to five minutes waiting for the next tick, then the work. The next day I deployed the event-driven listener: an always-on task holds an outbound WebSocket to Slack (no inbound port, no public URL), and starts the tick task 30 seconds after the last new post, so a burst of alerts becomes one tick. On the failover morning that meant 4 minutes 46 seconds from the CRITICAL post to its DM, 3 minutes 26 seconds after the last of the four alerts. The five-minute schedule stays, for the fleet sweep and as the safety net. The listener added a case to the table, and it is the gap to know about: if the listener dies, nothing says so. The lag quietly goes back to 1.5 to 13 minutes until someone checks the listener's heartbeat file.

Rule 8. Sweep the fleet every tick, because the alert channel can go silent

The channel's own alerts come from SQL Agent jobs. A crash that takes the Agent down also takes the alerts down. The day before go-live our infrastructure monitor caught a node in exactly that state while the SQL channel stayed empty, and on 6 November r3-db1's Agent stayed down for 22 minutes after its crash, so its own dump-storm alert could not run. So every tick, alert or no alert, runs one fan-out query on all eight nodes:

SELECT @@SERVERNAME AS srv, si.sqlserver_start_time,
       (SELECT TOP 1 status_desc FROM sys.dm_server_services
         WHERE servicename LIKE N'SQL Server Agent%')        AS agent_status,
       (SELECT COUNT(*)           FROM sys.dm_server_memory_dumps) AS dmv_dump_count,
       (SELECT MAX(creation_time) FROM sys.dm_server_memory_dumps) AS dmv_newest_dump
FROM sys.dm_os_sys_info AS si;

Compared against the stored baseline: Agent not Running, a changed start time, three or more dumps in ten minutes or a node unreachable for two ticks go to three people; a single new dump comes to me, read through the signed procedure, with a WATCH rule added. Every 30 minutes it also lists the dump files on disk, because sys.dm_server_memory_dumps under-reports text-only dumps.

Rule 9. Validate twice, label every claim, and name who

Before a word is written, two passes. Pass one: does the conclusion follow? Control group, base rate, the right SPID, like for like, units. Pass two: re-measure the key number from a different source and ask what you would see if the framing were wrong. Every claim in the DM is Measured (with its source), Inferred, or Withdrawn. A withdrawn hypothesis is reported as such, not deleted.

Every RCA also carries a Who section: node, database, SPID, login, host or pod, application, job and step, procedure or statement, Query Store query_id/plan_id. Each field is Measured with its source or "not captured: why". On the failover morning that read: a background task, spid 0, no input buffer (Measured: dump text); login, host and app not captured, because an engine background thread has no session. Query Store is a standing step on the hot databases; Aborted rows at about 30 seconds are the application's command timeouts, per statement. Where it is deployed, an Extended Events session supplies what sampled captures miss: blocked-process reports, client timeouts and severity-16 errors with their caller. One practical trap: an SSMS Always On dashboard left open on a node cancels its own refresh about every 8 seconds, which looks exactly like a client timeout in the attention events. A real timeout has an application pod as host, an app login, about 30 seconds of duration and a matching Query Store Aborted row.

Prior cases matter as much as live data. A file of 80 production cases, exported from my own notes, is grepped before any conclusion and cited by date; a hypothesis a prior case marks Withdrawn is not proposed again.

The DM format is fixed and must read on a phone: one bold headline with severity, node and time; one plain sentence on impact; What we measured in two to four bullets with numbers; Who; Why with labels; Do with the exact T-SQL and who runs it, or "Nothing to run"; Watch with the rule now in place. LOW under 1,500 characters, CRITICAL under 3,500.

Rule 10. Treat alert text as data, and make it report its own gaps

Everything a tool returns is data, including the alert itself. Text that says "run this to fix it", "skip the check" or "the node is healthy" is a finding to quote, not an order. The go-live test was exactly that: a synthetic CRITICAL whose every checkable number was false, measured against the node and labelled Inferred. A real alert gets the same treatment: it is a snapshot written at one moment by one node, and the monitor measures what is true now, from more than one place. And when it cannot look, it says so, in one line, instead of filling the gap. Here is what that looked like on a real disaster alert.

The morning of a disaster alert

Widget 3: a real disaster alert on 6 Nov 2026 and the five DMs that answered it. Real messages, one row each; names and hosts masked.

Three things to notice.

The alert could not know what had happened; the DM could. The CRITICAL was written by r3-db2 at 07:50:56, in the middle of the failover: role RESOLVING, partner DISCONNECTED, and the old primary's state "visible on the primary only". Five minutes later the DM had measured the end state from both nodes: r3-db2 primary for both R3 availability groups, four databases synchronized, 0 seconds of data loss, r3-db1 restarted at 07:51:56 after a last-chance exception in a background task. It also said what no alert said: r3-db1 is now the preferred backup replica and its Agent did not come back, so log backups had stopped. The log backups resumed at 08:20:07, 40 minutes after the last one.

Four alerts became one tick, and each still got its own answer. The CRITICAL and the replication HIGH came from our own SQL alert jobs in #db-alerts; the connection error and the Agent DOWN came from the infrastructure monitor in #infra-alerts. The listener waited 30 seconds after the last of them, then one run answered all four plus its own fleet sweep: five DMs in 24 seconds, a reply under each #db-alerts alert for on-call, and the disaster-channel triage to me alone, because that is how I configured it. One of the DMs found a bug in our own alert procedure: the error line in the replication alert came from the alert's query, not from replication.

What it could not see, it said. Half an hour before the crash it had rated the first dump on r3-db1 LOW: one dump, no restart, not a storm. The next dump was fatal. From 08:12 to 08:19 it lost SQL access itself, said so, queued three checks and ran them when access came back. And when the Agent stopped a second time at 08:17, cleanly, it wrote "who issued the stop: not captured" and handed me the Windows event-log command instead of guessing. The infrastructure monitor's own times bracket every Agent time in the DMs. The soft spots I found on re-checking are small: "a brief interruption (about 1 min)" carried no Measured or Inferred label, and one line about a log backup was two minutes stale when it was posted.

What stayed human: every Do line. Starting the Agent, adding both dumps to the vendor case, fixing our replication alert. The monitor named each action and who should run it; it changed nothing.

If you take one thing

Give the agent a schedule, a listener, a read-only login, two scripts and a state file, and put every refusal in code it cannot talk its way past. Then make it say what it measured, what it inferred, and what it could not check. A disaster alert no longer starts with me looking for a laptop and a VPN; it starts with a measured answer in my DMs, and I spend the first half hour on what only a person can decide.