In Part 1, I built the fence. In Part 2, I let Claude Code run the report without me, using a SQL login that could only read one table. The SQL ran, the output was saved, and the agent logged what it did. It also made a couple of guesses about its own execution that weren't supported by the evidence.
In this final part, I want to make my application more reliable, verify what Claude Code actually executes, and test whether the report can accurately distinguish meaningful changes from noise between sp_Blitz collections. Just as importantly, I want to examine whether the agent can correctly interpret those findings, or whether its conclusions still require a DBA's judgment.
Step 1: Give sp_Blitz a Sensible Schedule
Part 2 left me with a SQL Agent job, DBA - sp_Blitz to BlitzResults, running every 30 minutes. That's 48 collections a day --- far more than I needed for this experiment. One daily collection at 6:00 AM is enough. The Claude Code report can still compare the two most recent collections, but now those collections should normally be a day apart rather than 30 minutes.
I updated the existing SQL Agent schedule rather than creating another job:
It was only a schedule change. Nothing changed about the report's SQL Server login or its permissions. BlitzReader still only reads the results table; the SQL Agent job collects the findings.
Step 2: Keep the Last 30 Days
Before changing anything else, I checked the BlitzResults table for what I'd already collected. It held 5,365 rows across 147 collections and occupied about 2 MB. Small today, but a scheduled collection shouldn't grow forever just because nobody remembers to clean it up.
I chose a simple 30-day retention using CheckDate, which also identifies a collection in the report. This is my cleanup procedure:
CREATE OR ALTER PROCEDURE dbo.usp_CleanupBlitzResults
AS
BEGIN
SET NOCOUNT ON;
DELETE dbo.BlitzResults
WHERE CheckDate < DATEADD(DAY, -30, SYSDATETIMEOFFSET());
END;
GO
I ran it manually and then added it in as a second step to the existing Agent job:
The sp_Blitz call proceeds to the cleanup on success, and the cleanup ends the job. The procedure executed successfully in my manual test, but we'd still want to verify that in job history after it is run under the BlitzRunner security context.
Step 3: When a Change Isn't a Change
With the collection schedule and cleanup taken care of, I turned my focus to the report itself. Collecting the findings is one thing, but the report also needs to tell me what's actually changed between this run and the last. Otherwise, I'll be spending time reviewing differences that don't really matter.
My application runs a report that compares the last two sp_Blitz collections and classifies findings as New, Fixed, Changed, or Same. That worked reasonably well, but I noticed something in the results. My app was reporting changes that weren't really changes.
One example was CheckID 94, 'Agent Jobs Without Failure Emails'. The report classified it as 'Changed' even though the job still had no failure notification configured. Looking closer at the 'Details' values, I found that it was just because the job's last-execution timestamp had changed. Since the report compared the entire 'Details' value, it treated that timestamp difference as a change to the finding itself.
I changed the comparison for CheckID 94 so it uses the job name instead of the entire 'Details' value. The job name is extracted from the text before ' has not been set up', leaving out the execution timestamp that was causing the false change. This gives the report a consistent value to compare between collections, so the same job can be classified as 'Same' even when its execution timestamp differs. The report still displays the original 'Details' values so I can see exactly what sp_Blitz recorded.
CASE
WHEN b.CheckID = 94
AND b.Details LIKE '% has not been set up%'
THEN LEFT(b.Details,
CHARINDEX(' has not been set up', b.Details) - 1)
ELSE b.Details
END AS CompareDetails
That fixed the immediate false positive, but it raised another question. What happens when sp_Blitz returns multiple findings for the same check, and one of those findings disappears while another appears? Could my report distinguish the two, or would it incorrectly treat them as a single finding that changed?
Suppose CheckID 4, 'Sysadmins', reports Alice and Bob in one collection, and then Bob and Charlie in the next. Alice's finding has disappeared (Fixed), Charlie's has appeared (New), and Bob's remains unchanged (Same). Without matching by login name, the report could incorrectly treat Alice and Charlie as one finding that Changed.
To fix this, I updated the matching logic to identify each finding by its login name, just as I used the job name for CheckID 94. This prevents the report from treating two different logins or jobs as a single finding that Changed.
-- Identity used for CheckID 4
WHEN b.CheckID = 4
AND b.Details LIKE 'Login [[]%] is a sysadmin%'
THEN LEFT(b.Details,
CHARINDEX('] is a sysadmin', b.Details))
-- Additional condition in the leftover-row join
AND (
COALESCE(c.CheckID, p.CheckID) NOT IN (4, 94)
OR c.CompareDetails = p.CompareDetails
)
The actual comparison returned 39 findings:
| Status | Before | After |
|---|---|---|
| New | 2 | 2 |
| Changed | 1 | 0 |
| Fixed | 1 | 1 |
| Same | 35 | 36 |
| Total | 39 | 39 |
I also tested a replacement scenario with synthetic rows. Previous run: Alice and Bob. Current run: Bob and Charlie. The expected answer is Bob = Same, Alice = Fixed, Charlie = New. That's what the revised matching approach returned. No SQL Server logins were changed for the test.
Status PreviousDetails LatestDetails Same Login [Bob] Login [Bob] Fixed Login [Alice] NULL New NULL Login [Charlie]
This isn't a universal solution for every sp_Blitz finding. It just addresses two checks whose identities I could establish from the actual data. Other checks with multiple rows under the same key may still need their own matching rules.
Step 4: What Did the Agent Actually Execute?
With the comparison logic corrected, I wanted to verify the next part of the process: what Claude Code actually executed. I don't mean from its own activity log. I wanted SQL Server's record of the Claude Code execution.
I created a temporary Extended Events session to capture completed SQL batches and RPC calls under BlitzReader. Then I'd have Claude run the report and compare its account with the events SQL Server recorded.
CREATE EVENT SESSION [BlitzReaderAudit]
ON SERVER
ADD EVENT sqlserver.sql_batch_completed
(
ACTION
(
sqlserver.server_principal_name,
sqlserver.sql_text,
sqlserver.client_app_name
)
WHERE (sqlserver.server_principal_name = N'BlitzReader')
),
ADD EVENT sqlserver.rpc_completed
(
ACTION
(
sqlserver.server_principal_name,
sqlserver.sql_text,
sqlserver.client_app_name
)
WHERE (sqlserver.server_principal_name = N'BlitzReader')
)
ADD TARGET package0.ring_buffer
(
SET max_memory = 4096
)
WITH
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION [BlitzReaderAudit]
ON SERVER STATE = START;
GO
Now I returned to my existing C:\BlitzApp project in Claude Code and asked it first to list for me the project files and read CLAUDE.md, without changing or executing anything. Everything listed as expected. Then I asked it to run the existing report.sql, save the output in the reports folder, and record the attempt in attempts.md. Claude requested permission before executing the commands, and I gave my approval.
Claude reported that everything completed successfully. It provided the command it had executed, confirmed that the report was saved, and said the attempt was recorded in attempts.md:
Claude reported 39 findings, including no 'Changed' findings. But the command and the reported result were still Claude's account. The Extended Events session would tell me what SQL had actually been run.
For SQL Server's version of events, the XE session captured three completed SQL batches executed as BlitzReader, from SQLCMD, at 2:10 PM:
The first two batches were SQLCMD session settings. The third contained report.sql, including the corrected CheckID 4 and 94 comparisons. SQL Server recorded that batch executing under BlitzReader, from SQLCMD, at the time Claude reported.
That was the confirmation I wanted: the AI agent's claim about SQL execution matched SQL Server's evidence. Extended Events couldn't verify the report file or attempts.md, but it did establish what ran against the database.
Step 5: The SQL Was Right. Claude's Explanation Wasn't.
Running the right SQL doesn't mean interpreting the results correctly. Claude's summary made that distinction clear.
Claude warned about a five-day gap between collections and suggested the server or Agent job was probably down. There was no five-day gap. The collections were taken on October 31 at 12:30 PM and 1:00 PM, just 30 minutes apart. Claude had mistaken a job's last-execution timestamp in the Details column for a collection date. The report was correct; the explanation was not.
Claude did better with CheckID 160, 'Many Plans for One Query'. The finding disappeared after a server restart, and the report classified it as 'Fixed'. Claude correctly recognized that clearing the plan cache didn't necessarily fix the underlying parameterization problem.
That's the distinction: the report shows what changed between collections. Explaining why it changed takes more evidence. Even when the SQL is right, the agent's interpretation still needs checking.
Step 6: Put It to the Test
So far, I've corrected false changes and verified what the AI agent executed. But I still wanted to be sure that my application correctly reports a new sp_Blitz finding when a real SQL Server health problem is detected.
I chose PAGE_VERIFY, a setting used to detect corruption in database pages, where CHECKSUM is the recommended setting and NONE means that no checks are performed. I created a disposable database, BlitzPageVerifyTest, with PAGE_VERIFY CHECKSUM and established a baseline sp_Blitz collection.
Then I changed the setting to NONE and ran sp_Blitz again:
ALTER DATABASE BlitzPageVerifyTest SET PAGE_VERIFY NONE;
As expected, sp_Blitz detected the problem as Priority 50, 'Page Verification Not Optimal'.
Now, to verify that my app reports this to me as a new finding, I asked Claude Code to run the existing report.sql without any modifications. My app compared the two most recent collections and returned 43 findings: 1 New, 0 Changed, 0 Fixed, and 42 Same. The new finding was the PAGE_VERIFY problem I'd deliberately introduced.
Then I enabled the correct page verification:
ALTER DATABASE BlitzPageVerifyTest SET PAGE_VERIFY CHECKSUM;
And after another sp_Blitz collection, the Priority 50 finding disappeared. I had Claude Code run the report again, and this time it returned 1 Fixed, 0 New, 0 Changed, and 42 Same.
That's what I wanted to see. sp_Blitz detected the configuration problem, and my application correctly reported it as New when it appeared and Fixed when it disappeared. This was a very simple test, but it demonstrated exactly what I wanted: the application could distinguish a meaningful change in SQL Server's health from the noise between collections.
Limits
- Daily collection isn't continuous monitoring. The report compares the latest two available collections. If one is missed, the interval may be longer than a day.
- Matching is check-specific. I verified the identity rules for CheckIDs 4 and 94. I haven't proven that every other repeated CheckID can be safely paired by row order.
- XE has a boundary. It confirmed the SQL submitted under BlitzReader, not the contents of files written on Windows or the accuracy of Claude's narrative.
Where I Landed
The job now has a daily schedule and a retention procedure. The report no longer mistakes a changing timestamp for a changed finding, and it can distinguish one sysadmin login disappearing from another appearing. Extended Events also gave me independent evidence of what Claude Code executed.
The final test brought everything together. I introduced a real database configuration problem, sp_Blitz detected it, and my application correctly reported it as New. When I corrected the setting, my app reported it as Fixed. Pretty simple, yes, but it did exactly what I wanted it to do. Very controlled. Very manageable.
Now I want to take this much further. The next project will automate the sp_Blitz health check, review the prioritized findings, and return recommended remediation for each, with the option to list the recommended corrective actions or execute them. That's a much bigger undertaking. Every recommendation will depend on the finding, the SQL Server environment, and the conditions under which a particular correction is appropriate. And before anything executes, I'll need to establish a new set of boundaries around what the AI agent is permitted to do.
And that's where the distinction between reporting a change and understanding it becomes even more important. Claude Code executed the right SQL and correctly reported the PAGE_VERIFY changes, yet earlier it had misinterpreted valid evidence and reached the wrong conclusion. The fence controls what the agent can do. It doesn't guarantee that what the agent tells me is correct. That responsibility still belongs to the DBA.
More to Read
sqlfingers: Build the Fence Before Letting in the AI, Part 1
sqlfingers: Build the Fence Before Letting in the AI, Part 2
Brent Ozar: SQL Server First Responder Kit
Microsoft: Extended Events
No comments:
Post a Comment