Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Switch icon
Pause icon
Loop icon
Fail icon
Schedule icon

SQL Server Database Integrity DBCC Repair Guard

Run DBCC CHECKDB on SQL Server, rebuild only nonclustered index damage with no data loss, refuse the destructive repair and re-check.

Categories
DataInfrastructure

SQL Server tells you a database is corrupt and then offers you a repair that deletes data. DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS deallocates the pages it cannot read, deletes the rows on them and calls the result consistent. It is the fastest way to turn a recoverable incident into permanent data loss. It is also the option most maintenance scripts reach for because it is the one that makes the error go away.

There is exactly one class of corruption that can be fixed with no data loss at all: damage confined to nonclustered indexes. A nonclustered index holds no copy of anything unique, every key and included column is also in the clustered index or heap, so throwing the index away and rebuilding it loses nothing. This blueprint finds that class specifically, fixes it behind a gate and an approval, re-runs DBCC CHECKDB and fails the run if anything is left. For every other class it refuses, says why, names the restore path and stops.

This blueprint was created by zkasuran.

Findings

Two things we measured on SQL Server 2022 while building this, both of which shape the design.

CHECKDB's own RepairLevel column is not a safe classifier. A single corrupt page in a nonclustered index produced four errors. Two of them (8939 and 8928) came back with RepairLevel set to repair_allow_data_loss, because the page failed its checksum and CHECKDB cannot read a page it cannot read. Trusting that column would mean running a data destroying repair on damage that an ALTER INDEX fixes perfectly. So the classification here never reads RepairLevel. It resolves the ObjectId and IndexId of every error against the catalog views and asks what the damaged object actually is.

A plain ALTER INDEX ... REBUILD cannot repair a corrupt index. It scans the existing index to build the new one, hits the bad page and fails with error 824. The sequence that works is ALTER INDEX ... DISABLE first, which discards the index pages and keeps the definition, then ALTER INDEX ... REBUILD, which now reads only the clustered index or heap. That is why remediation is two statements per index rather than one.

Safety

  • Read only by default. dry_run is true and auto_remediate is false, so a normal run checks, classifies and alerts.
  • An index is rebuilt only when auto_remediate is true, dry_run is false and a human approves the Pause.
  • REPAIR_ALLOW_DATA_LOSS is never run by this flow, under any input combination. confirm_data_loss_repair controls one thing only: whether the flow prints the statement sequence for a human to run by hand.
  • One unsafe object is enough to refuse the whole run. A nonclustered index is rebuilt by reading the clustered index or heap, so rebuilding while that structure is damaged would spread the damage.
  • max_index_rebuilds caps how many indexes one run will touch. Damage wider than that usually means failing storage, not one bad page.
  • Corruption outside the safe class fails the run on purpose, so a nightly check cannot end green on a database that needs a restore.

How it works

  1. check (io.kestra.plugin.jdbc.sqlserver.Query, read only) runs DBCC CHECKDB (N'<database>') WITH NO_INFOMSGS, TABLERESULTS, optionally with PHYSICAL_ONLY. TABLERESULTS returns the messages as rows carrying the error number, the severity, the object id, the index id, the page and the repair level.
  2. findings (io.kestra.plugin.core.output.OutputValues) keeps the rows at severity 16 or above. The severity 10 rows (8989 and 8990) are CHECKDB's own summary counts, not findings. It builds the list of damaged object and index id pairs, plus a sentinel pair so the list is never empty when every error is allocation level and carries no object.
  3. route (io.kestra.plugin.core.flow.If) splits on whether anything was found. The clean branch logs and ends.
  4. classify (read only) joins every damaged pair against sys.indexes, sys.objects and sys.schemas in the target database. Safe means index_id above 1, type = 2 (a nonclustered B tree) on a user table. An id that resolves to nothing is unsafe. It builds the DISABLE and REBUILD statements with QUOTENAME.
  5. triage sets the verdict. SAFE needs every damaged object to be a nonclustered index, at least one of them to be, plus a count within max_index_rebuilds. Anything else is REFUSE. alert posts the whole picture to Slack.
  6. decide (io.kestra.plugin.core.flow.Switch):
    • SAFE: safe_gate checks the two inputs, approval_gate (Pause) waits for a human, then remediate (Loop, concurrencyLimit: 1) runs DISABLE then REBUILD per index through the target database's own sys.sp_executesql. recheck runs DBCC CHECKDB again, still_disabled counts planned indexes left disabled and verify_result fails the run if any error or disabled index is left.
    • REFUSE: refuse_report logs what was found, why there is no lossless fix for it and what REPAIR_ALLOW_DATA_LOSS would do. confirm_gate prints the repair sequence only when confirm_data_loss_repair is true. refuse_fail fails the run.
  7. errors posts a Slack alert when the check cannot run or the run fails.

What you get

  • A real integrity check, with every error attributed to a named table and index rather than left as a message number.
  • The one class of corruption that can be fixed losslessly, fixed, with a DBCC CHECKDB re-run as the gate rather than the plugin's success flag.
  • A refusal with reasons for everything else, pointing at the restore path instead of the repair that deletes rows.
  • A Slack alert carrying the counts for both classes, plus a separate alert when the run fails.

Who it's for

  • DBAs who want the nightly integrity check to act on the safe class on its own and escalate the rest.
  • Platform teams running SQL Server in containers where SQL Server Agent is absent.
  • Anyone who has watched a maintenance script reach for REPAIR_ALLOW_DATA_LOSS because it was the option that cleared the error.

Why orchestrate this with Kestra

Corruption triage is a decision tree, not a command. Kestra runs the check, resolves every error to a real object, picks the class, applies only the lossless fix behind two inputs and a human approval, then re-runs the check and fails loudly if the fix did not land. The refusal is a first class path with its own report rather than an unhandled case. The run history keeps the error list before and after, which is what you need when you have to explain afterwards what was lost and what was not.

Tested end to end

On Kestra 2.0.5 against SQL Server 2022 Developer Edition (16.0.4295.3), with a local HTTP sink standing in for Slack. Corruption was induced on purpose in a disposable container: a leaf page of a nonclustered index was located with sys.dm_db_database_page_allocations, then overwritten with DBCC WRITEPAGE in single user mode, which breaks the page checksum. A heap page of a second table was corrupted the same way for the refusal path. A second database with the same schema was left untouched to prove precision. Error counts below come from DBCC CHECKDB run directly in sqlcmd, not from the flow.

Run Inputs Result
1 defaults, untouched database clean branch, 0 errors. The same check on the damaged database found 4
2 damaged nonclustered index, defaults 4 errors, every one on the same nonclustered index, verdict SAFE, preview printed both statements, Slack alerted. DBCC CHECKDB still reported 4 errors afterwards, so the dry run changed nothing
3 auto_remediate: true, dry_run: false, approved at the Pause DISABLE then REBUILD ran, recheck returned 0 errors. 20000 rows through the rebuilt index against 20000 in the clustered index, 20000 distinct keys, so nothing was lost
4 same, declined at the Pause declined logged, no statement ran, the same 4 errors were still there
5 nonclustered damage plus a damaged heap page verdict REFUSE on 6 errors with 1 object outside the safe class, no statement ran, the run failed, the errors block posted to Slack. The safe index was deliberately left alone too
6 same, confirm_data_loss_repair: true the REPAIR_ALLOW_DATA_LOSS sequence was printed with its warning. The flow still refused to run it, still failed the run, left both classes untouched
7 nonclustered damage with the database set READ_ONLY DISABLE and REBUILD were both rejected, the loop still reported success, recheck found the same 4 errors so verify_result failed the run
8 max_index_rebuilds: 0 verdict REFUSE on the ceiling, nothing ran, the run failed
9 check_scope: PHYSICAL_ONLY on the same damage 2 errors instead of 4 (the checksum failures, with the logical chain errors skipped), same classification, verdict SAFE
10 unreachable server check failed, the errors block posted to Slack

Prerequisites

  • A SQL Server instance you can reach over JDBC.
  • A login in db_owner on the target database, which is what DBCC CHECKDB requires, plus ALTER on the tables whose indexes may be rebuilt.
  • A Slack incoming webhook.

Secrets

  • SQLSERVER_PASSWORD: password for the login in user. The password input defaults to this secret.
  • SLACK_WEBHOOK_URL: Slack incoming webhook, used by alert and the errors block.
  • In Kestra OSS, provide each one as a base64 encoded environment variable prefixed SECRET_.

Inputs

Input Default Purpose
url jdbc:sqlserver://localhost:1433 SQL Server instance
user sa Login used for the check
password {{ secret('SQLSERVER_PASSWORD') }} Password for the login
database app Database DBCC CHECKDB runs against
check_scope FULL FULL or PHYSICAL_ONLY
max_index_rebuilds 25 Ceiling on how many indexes one run will rebuild
auto_remediate false Allow the nonclustered index rebuild
dry_run true Must be false before any index is rebuilt
confirm_data_loss_repair false Print the REPAIR_ALLOW_DATA_LOSS sequence for a human. Never runs it

Quick start

  1. Add the SQLSERVER_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow, set url, user and database.
  3. Run it with the defaults. On a healthy database it logs and ends.
  4. If it reports nonclustered index damage, run again with auto_remediate: true and dry_run: false, then approve the Pause.
  5. Enable the nightly trigger, which always runs in safe mode.

How to extend

  • Loop over sys.databases to check every database on the instance, one execution per database, so one corrupt database does not hide another.
  • Read msdb.dbo.suspect_pages alongside the check to see how long a page has been failing.
  • Add a RESTORE DATABASE ... PAGE = path for the unsafe class, driven from a backup catalog, so the refusal can hand over to a page restore instead of only naming it.
  • Record each verdict in a KV store to spot a database that keeps coming back, which is the signal for failing storage rather than one bad page.
  • Rebuild with WITH (ONLINE = ON) on Enterprise edition. The DISABLE step still takes the index offline.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.