id: sqlserver-database-integrity-dbcc-repair-guard
namespace: company.team
description: |
Run DBCC CHECKDB on a SQL Server database, read every error back through
TABLERESULTS and classify it by the object it actually hit. Corruption confined to
nonclustered indexes is the one class that can be fixed with no data loss, so the
flow disables and rebuilds those indexes from the clustered index or heap behind a
safe by default gate and a human approval, then runs DBCC CHECKDB again and fails
the run if any error is left. Every other class (clustered index, heap, allocation,
system table, PFS or GAM page) is refused on purpose. The flow never runs
REPAIR_ALLOW_DATA_LOSS, it names RESTORE FROM BACKUP as the first response and it
fails the run so a scheduled check cannot end green on a database that needs a
restore.
inputs:
- id: url
type: STRING
displayName: JDBC URL
description: Points at the SQL Server instance. The checks name the database
explicitly, so connecting to master is enough.
defaults: "jdbc:sqlserver://localhost:1433"
- id: user
type: STRING
displayName: SQL Server login
defaults: sa
- id: password
type: STRING
displayName: SQL Server password
description: Defaults to the SQLSERVER_PASSWORD secret. Override only for local testing.
defaults: "{{ secret('SQLSERVER_PASSWORD') }}"
- id: database
type: STRING
displayName: Database to check
description: The database DBCC CHECKDB runs against. One database per run, so a
failure is attributable.
defaults: app
- id: check_scope
type: SELECT
displayName: CHECKDB scope
description: FULL runs every check. PHYSICAL_ONLY skips the logical checks,
which is far cheaper on a large database and makes a frequent schedule
practical.
values:
- FULL
- PHYSICAL_ONLY
defaults: FULL
- id: max_index_rebuilds
type: INT
displayName: Maximum indexes to rebuild in one run
description: A ceiling on the blast radius. When more nonclustered indexes than
this are damaged the flow refuses and reports instead, because damage that
wide usually means failing storage rather than one bad page.
defaults: 25
- id: auto_remediate
type: BOOL
displayName: Auto remediate
description: Allow the nonclustered index rebuild. It never allows a data
destroying repair.
defaults: false
- id: dry_run
type: BOOL
displayName: Dry run
description: Must be false, together with auto_remediate, before any index is rebuilt.
defaults: true
- id: confirm_data_loss_repair
type: BOOL
displayName: Confirm you want the data destroying repair considered
description: >-
Separate from auto_remediate on purpose. Leave false and the flow will not
even print the REPAIR_ALLOW_DATA_LOSS sequence for corruption outside the
safe class. Set true and it prints the exact statements with the single
user mode they need, for a human to run by hand after deciding against a
restore. The flow never runs them itself.
defaults: false
variables:
check_sql: |
DBCC CHECKDB (N'{{ inputs.database | replace({"'": "''"}) }}') WITH NO_INFOMSGS, TABLERESULTS{% if inputs.check_scope == 'PHYSICAL_ONLY' %}, PHYSICAL_ONLY{% endif %}
classify_sql: |
SELECT
e.object_id AS object_id,
e.index_id AS index_id,
COALESCE(s.name, N'unresolved') AS schema_name,
COALESCE(o.name, N'unresolved') AS object_name,
COALESCE(i.name, N'unresolved') AS index_name,
COALESCE(i.type_desc, N'NO MATCHING INDEX') AS index_type,
COALESCE(o.type_desc, N'NO MATCHING OBJECT') AS object_type,
CASE WHEN i.index_id > 1 AND i.type = 2 AND o.type = 'U' AND o.is_ms_shipped = 0 THEN 1 ELSE 0 END AS safe_to_rebuild,
CASE WHEN i.index_id > 1 AND i.type = 2 AND o.type = 'U' AND o.is_ms_shipped = 0
THEN CONCAT('EXEC ', QUOTENAME(DB_NAME(e.db_id)), '.sys.sp_executesql N', CHAR(39),
'ALTER INDEX ', REPLACE(QUOTENAME(i.name), CHAR(39), CHAR(39) + CHAR(39)),
' ON ', REPLACE(QUOTENAME(s.name), CHAR(39), CHAR(39) + CHAR(39)), '.',
REPLACE(QUOTENAME(o.name), CHAR(39), CHAR(39) + CHAR(39)), ' DISABLE', CHAR(39))
ELSE N'' END AS disable_sql,
CASE WHEN i.index_id > 1 AND i.type = 2 AND o.type = 'U' AND o.is_ms_shipped = 0
THEN CONCAT('EXEC ', QUOTENAME(DB_NAME(e.db_id)), '.sys.sp_executesql N', CHAR(39),
'ALTER INDEX ', REPLACE(QUOTENAME(i.name), CHAR(39), CHAR(39) + CHAR(39)),
' ON ', REPLACE(QUOTENAME(s.name), CHAR(39), CHAR(39) + CHAR(39)), '.',
REPLACE(QUOTENAME(o.name), CHAR(39), CHAR(39) + CHAR(39)), ' REBUILD', CHAR(39))
ELSE N'' END AS rebuild_sql
FROM (VALUES {{ outputs.findings.values.pair_values }}) AS e(db_id, object_id, index_id)
LEFT JOIN [{{ inputs.database | replace({"]": "]]"}) }}].sys.indexes i ON i.object_id = e.object_id AND i.index_id = e.index_id
LEFT JOIN [{{ inputs.database | replace({"]": "]]"}) }}].sys.objects o ON o.object_id = e.object_id
LEFT JOIN [{{ inputs.database | replace({"]": "]]"}) }}].sys.schemas s ON s.schema_id = o.schema_id
WHERE e.object_id <> -1
ORDER BY safe_to_rebuild, e.object_id, e.index_id
disabled_sql: |
SELECT COUNT(*) AS still_disabled
FROM (VALUES {{ outputs.findings.values.pair_values }}) AS e(db_id, object_id, index_id)
JOIN [{{ inputs.database | replace({"]": "]]"}) }}].sys.indexes i ON i.object_id = e.object_id AND i.index_id = e.index_id
WHERE i.is_disabled = 1
tasks:
- id: check
type: io.kestra.plugin.jdbc.sqlserver.Query
description: >-
Read only. DBCC CHECKDB without a repair option reads an internal snapshot
of the database and changes nothing. TABLERESULTS turns the messages into
a result set carrying the object id, the index id, the page and the repair
level per error.
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: FETCH
sql: "{{ render(vars.check_sql) }}"
- id: findings
type: io.kestra.plugin.core.output.OutputValues
description: >-
Keep the rows CHECKDB raised at severity 16 or above. The level 10 rows
(8989 and 8990) are its own summary counts, not findings. The object and
index id pairs become the lookup list for the classifier, with a sentinel
row so the list is never empty when every error is allocation level and
carries no object.
values:
actionable: "{{ (outputs.check.rows ?? []) | jq('[.[] | select((.Level // 0) >=
16)] | length') | first }}"
pair_values: >-
{{ (outputs.check.rows ?? []) | jq('([.[] | select((.Level // 0) >= 16)
| "(\(.DbId // 0),\(.ObjectId // 0),\(.IndexId // -2))"] | unique) +
["(-1,-1,-1)"] | join(",")') | first }}
error_list: >-
{{ (outputs.check.rows ?? []) | jq('[.[] | select((.Level // 0) >= 16) |
"msg \(.Error) level \(.Level) object \(.ObjectId) index \(.IndexId)
page (\(.File):\(.Page)) repair_level \(.RepairLevel // "none")"] |
join("; ")') | first }}
- id: summary
type: io.kestra.plugin.core.log.Log
message: >-
DBCC CHECKDB {{ inputs.check_scope }} on {{ inputs.database }} at {{
inputs.url }}: {{ outputs.findings.values.actionable }} error(s) at
severity 16 or above. {{ outputs.findings.values.error_list }}
- id: route
type: io.kestra.plugin.core.flow.If
condition: "{{ (outputs.findings.values.actionable | number) > 0 }}"
then:
- id: classify
type: io.kestra.plugin.jdbc.sqlserver.Query
description: >-
Read only. Resolves every damaged object and index id against the
catalog views of the target database. An index is safe to rebuild only
when it is a nonclustered B tree (index_id above 1 and type 2) on a
user table, because such an index holds no data of its own and is
reconstructed from the clustered index or heap. Anything else,
including an id that resolves to nothing, is unsafe. The ALTER INDEX
statements are built here with QUOTENAME so the server quotes the
identifiers.
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: FETCH
sql: "{{ render(vars.classify_sql) }}"
- id: triage
type: io.kestra.plugin.core.output.OutputValues
description: >-
The verdict is SAFE only when every damaged object is a nonclustered
index, at least one of them is and the count is within the ceiling.
One unsafe object is enough to refuse the whole run, because a
nonclustered index is rebuilt by reading the clustered index or heap.
Reading a structure that is itself damaged would spread the damage
rather than repair it.
values:
verdict: "{{ outputs.classify.rows | jq('if ([.[] | select(.safe_to_rebuild ==
0)] | length) > 0 then \"REFUSE\" elif ([.[] |
select(.safe_to_rebuild == 1)] | length) == 0 then \"REFUSE\" elif
([.[] | select(.safe_to_rebuild == 1)] | length) > ' ~
inputs.max_index_rebuilds ~ ' then \"REFUSE\" else \"SAFE\" end') |
first }}"
safe_count: "{{ outputs.classify.rows | jq('[.[] | select(.safe_to_rebuild ==
1)] | length') | first }}"
unsafe_count: "{{ outputs.classify.rows | jq('[.[] | select(.safe_to_rebuild ==
0)] | length') | first }}"
plan: >-
{{ outputs.classify.rows | jq('[.[] | select(.safe_to_rebuild == 1)
| {index: "\(.schema_name).\(.object_name).\(.index_name)", disable:
.disable_sql, rebuild: .rebuild_sql}]') | first | toJson }}
safe_list: >-
{{ outputs.classify.rows | jq('[.[] | select(.safe_to_rebuild == 1)
| "\(.schema_name).\(.object_name).\(.index_name) (\(.index_type))"]
| join("; ")') | first }}
unsafe_list: >-
{{ outputs.classify.rows | jq('[.[] | select(.safe_to_rebuild == 0)
| "object \(.object_id) index \(.index_id)
\(.schema_name).\(.object_name) (\(.object_type) / \(.index_type))"]
| join("; ")') | first }}
- id: alert
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'SQL Server ' ~ inputs.database ~ ' at ' ~ inputs.url ~ ': DBCC CHECKDB found ' ~ outputs.findings.values.actionable ~ ' error(s). Nonclustered index damage safe to rebuild: ' ~ outputs.triage.values.safe_count ~ ' (' ~ outputs.triage.values.safe_list ~ '). Damage outside that class: ' ~ outputs.triage.values.unsafe_count ~ ' (' ~ outputs.triage.values.unsafe_list ~ '). Verdict ' ~ outputs.triage.values.verdict ~ '.'} | toJson }}
- id: decide
type: io.kestra.plugin.core.flow.Switch
value: "{{ outputs.triage.values.verdict }}"
cases:
SAFE:
- id: safe_gate
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
then:
- id: approval_gate
type: io.kestra.plugin.core.flow.Pause
description: >-
Hold the run until a human approves. Rebuilding an index
takes it out of service while it is rebuilt, so the sequence
never starts on its own.
pauseDuration: PT2H
behavior: FAIL
onResume:
- id: approved
type: BOOL
required: false
description: Set true to disable and rebuild the listed nonclustered indexes.
Anything else leaves them alone.
- id: apply
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
then:
- id: remediate
type: io.kestra.plugin.core.flow.Loop
description: >-
Serial on purpose, concurrencyLimit 1, so two rebuilds
never overlap on the same table. DISABLE comes first
because it is what makes the repair possible: a plain
ALTER INDEX REBUILD scans the existing index and dies on
the damaged page with error 824, while DISABLE throws
the index pages away and leaves the definition, so the
REBUILD that follows reads only the clustered index or
heap. Each statement runs through the target database's
own sys.sp_executesql, as its own autocommitting Query,
because ALTER INDEX cannot run inside an explicit
transaction.
concurrencyLimit: 1
values: "{{ outputs.triage.values.plan }}"
tasks:
- id: disable_index
type: io.kestra.plugin.jdbc.sqlserver.Query
description: Discard the damaged index pages. The index definition stays, the
data stays in the clustered index or heap.
allowFailure: true
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: NONE
sql: "{{ fromJson(item.value).disable }}"
- id: rebuild_index
type: io.kestra.plugin.jdbc.sqlserver.Query
description: Rebuild the index from the clustered index or heap. No row is read
from the damaged pages, so nothing is lost.
allowFailure: true
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: NONE
sql: "{{ fromJson(item.value).rebuild }}"
- id: recheck
type: io.kestra.plugin.jdbc.sqlserver.Query
description: >-
The gate. Run DBCC CHECKDB again over the whole
database, not just the indexes that were touched, so a
rebuild that reported success while the database is
still corrupt cannot pass.
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: FETCH
sql: "{{ render(vars.check_sql) }}"
- id: still_disabled
type: io.kestra.plugin.jdbc.sqlserver.Query
description: >-
Count the planned indexes that are still disabled. A
REBUILD that failed after its DISABLE leaves the index
disabled. CHECKDB skips a disabled index, so the recheck
alone would pass.
url: "{{ inputs.url }}"
username: "{{ inputs.user }}"
password: "{{ inputs.password }}"
trustServerCertificate: true
fetchType: FETCH_ONE
sql: "{{ render(vars.disabled_sql) }}"
- id: verify_result
type: io.kestra.plugin.core.flow.If
condition: "{{ ((outputs.recheck.rows ?? []) | jq('[.[] | select((.Level // 0)
>= 16)] | length') | first) > 0 or
outputs.still_disabled.row.still_disabled > 0 }}"
then:
- id: remediation_failed
type: io.kestra.plugin.core.execution.Fail
errorMessage: >-
DBCC CHECKDB still reports errors on {{
inputs.database }} or an index is still disabled ({{
outputs.still_disabled.row.still_disabled }}) after
rebuilding {{ outputs.triage.values.safe_count }}
index(es). The rebuild did not clear the corruption.
Either DISABLE or REBUILD was rejected (a unique
index that a foreign key points at cannot be
disabled), the database is read only, the login
lacks ALTER on the table or the damage reaches
further than the first check saw. Drop and create
the index by hand, then check again. If that leaves
errors, restore the database from the last known
good backup.
else:
- id: remediated
type: io.kestra.plugin.core.log.Log
message: >-
Rebuilt {{ outputs.triage.values.safe_count }}
nonclustered index(es) on {{ inputs.database }}: {{
outputs.triage.values.safe_list }}. DBCC CHECKDB {{
inputs.check_scope }} now reports no error at
severity 16 or above. No row was read from a damaged
page, so the table data is untouched.
else:
- id: declined
type: io.kestra.plugin.core.log.Log
message: >-
Approval was not given, so no index was rebuilt. The
findings stand on {{ inputs.database }}: {{
outputs.triage.values.safe_list }}. Until these indexes
are rebuilt, any query that uses one of them fails with
error 824.
else:
- id: preview
type: io.kestra.plugin.core.log.Log
message: >-
Safe to rebuild with no data loss, nothing changed
(auto_remediate={{ inputs.auto_remediate }}, dry_run={{
inputs.dry_run }}). {{ outputs.triage.values.safe_count }}
nonclustered index(es) on {{ inputs.database }}: {{
outputs.triage.values.safe_list }}. Would run: {{
fromJson(outputs.triage.values.plan) | jq('map("\(.disable)
then \(.rebuild)") | join(" | ")') | first }}
REFUSE:
- id: refuse_report
type: io.kestra.plugin.core.log.Log
level: ERROR
description: The loud report. This branch never repairs anything.
message: >-
REFUSED on {{ inputs.database }}. {{
outputs.findings.values.actionable }} error(s) at severity 16 or
above on {{ outputs.triage.values.safe_count }} nonclustered
index(es) plus {{ outputs.triage.values.unsafe_count }}
object(s) outside the one class that can be fixed without losing
data. {% if (outputs.triage.values.unsafe_count | number) > 0
%}Outside that class: {{ outputs.triage.values.unsafe_list }}.
Clustered index, heap, allocation, system table, PFS or GAM
damage holds the only copy of what is on the page, so there is
no lossless rebuild for it.{% else %}Every damaged object is a
nonclustered index, but there are more of them than
max_index_rebuilds ({{ inputs.max_index_rebuilds }}) allows.
Damage that wide usually means failing storage rather than one
bad page, so the rebuild is withheld until someone has looked at
the hardware.{% endif %} RESTORE FROM BACKUP is the correct
first response. Restore the database from the last known good
backup. The narrower option is to restore just the damaged pages
from it with RESTORE DATABASE ... PAGE = then roll the log
forward. DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is not a
repair in the ordinary sense. It deallocates pages it cannot
read and deletes the rows on them, drops the index and
constraint rows that pointed at them and makes the database
consistent by throwing data away. It also needs the database in
single user mode, which disconnects everyone. This flow will not
run it. Also check the physical cause before trusting the
storage again: read the SQL Server error log,
sys.dm_io_virtual_file_stats, the Windows or Linux system log
and msdb.dbo.suspect_pages.
- id: confirm_gate
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.confirm_data_loss_repair }}"
then:
- id: emit_repair_script
type: io.kestra.plugin.core.log.Log
level: WARN
description: Printed only because confirm_data_loss_repair is true. Still not
run by the flow.
message: >-
confirm_data_loss_repair is true, so here is the sequence a
DBA would run by hand once a restore has been ruled out. It
will delete data. ALTER DATABASE [{{ inputs.database }}] SET
SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB (N'{{
inputs.database }}', REPAIR_ALLOW_DATA_LOSS) WITH
NO_INFOMSGS, ALL_ERRORMSGS; ALTER DATABASE [{{
inputs.database }}] SET MULTI_USER; Take a full backup of
the database as it is first, because the repair is not
reversible. Afterwards reconcile what was lost: DBCC CHECKDB
reports the pages it deallocated. Constraints need
rechecking with DBCC CHECKCONSTRAINTS.
else:
- id: script_withheld
type: io.kestra.plugin.core.log.Log
message: >-
The REPAIR_ALLOW_DATA_LOSS sequence is withheld. Set
confirm_data_loss_repair to true to have this flow print it,
which is a separate decision from auto_remediate and is
still not an instruction to the flow to run it.
- id: refuse_fail
type: io.kestra.plugin.core.execution.Fail
errorMessage: >-
{{ inputs.database }} carries corruption this flow will not
repair{% if (outputs.triage.values.unsafe_count | number) > 0
%}: {{ outputs.triage.values.unsafe_list }}{% else %}: {{
outputs.triage.values.safe_count }} damaged nonclustered
index(es), past the max_index_rebuilds ceiling of {{
inputs.max_index_rebuilds }}{% endif %}. Restore from the last
known good backup. The run fails on purpose so a scheduled
integrity check cannot end green on a corrupt database.
defaults:
- id: unknown_verdict
type: io.kestra.plugin.core.execution.Fail
errorMessage: "Unexpected triage verdict {{ outputs.triage.values.verdict }} on
{{ inputs.database }}. Nothing was changed."
else:
- id: clean
type: io.kestra.plugin.core.log.Log
message: >-
DBCC CHECKDB {{ inputs.check_scope }} found no error at severity 16 or
above in {{ inputs.database }} on {{ inputs.url }}. Nothing to
rebuild, nothing to report.
errors:
- id: alert_flow_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'SQL Server integrity guard failed on ' ~ inputs.database ~ ' at ' ~ inputs.url ~ ' (execution ' ~ execution.id ~ '). Either DBCC CHECKDB could not run, the database carries corruption outside the safe class or the rebuild did not clear it. Check the logs.'} | toJson }}
triggers:
- id: nightly
type: io.kestra.plugin.core.trigger.Schedule
description: Shipped disabled. Always runs in safe mode (check and report only,
never a repair).
cron: "0 2 * * *"
disabled: true
inputs:
auto_remediate: false
dry_run: true
confirm_data_loss_repair: false