DBCC CHECKDB, Integrity Checks, Scheduling, Repair Boundaries, and Restore-First Philosophy

Learning outcomes

A backup job can succeed every night while corruption remains undiscovered for weeks. Chapter 15 established that a backup is not a recovery guarantee; this lesson adds structural integrity checking. DBCC CHECKDB is not a magical “repair database” command. Its primary value is detection: allocation, catalog, table/index, indexed-view, Service Broker and other consistency checks are combined so operators can identify damage early enough to restore from a known-good recovery point.

1. CHECKDB is a consistency test with resource consequences

DBCC CHECKDB combines allocation checks, table/view checks, catalog checks and several feature-specific validations. On ordinary user databases, SQL Server normally creates an internal database snapshot to obtain a transactionally consistent view without holding long-lived table locks. The sparse snapshot files live beside the database data files and can grow as source pages change while CHECKDB runs. If SQL Server cannot create that snapshot, or if TABLOCK is requested, locking behavior changes. Capacity planning therefore includes the source volume as well as tempdb.

USE master;GOIF DB_ID(N'ServiceHubOpsLab') IS NULLBEGIN    CREATE DATABASE ServiceHubOpsLab;END;GOALTER DATABASE ServiceHubOpsLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubOpsLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubOpsLab;GOIF SCHEMA_ID(N'lab21') IS NULL EXEC(N'CREATE SCHEMA lab21 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab21.RunAudit', N'U') IS NULLBEGIN  CREATE TABLE lab21.RunAudit  (    run_id bigint IDENTITY PRIMARY KEY,    run_name sysname NOT NULL,    started_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    finished_at datetime2(0) NULL,    outcome varchar(16) NOT NULL DEFAULT 'STARTED',    detail nvarchar(1000) NULL  );END;GOIF OBJECT_ID(N'lab21.WorkQueue', N'U') IS NULLBEGIN  CREATE TABLE lab21.WorkQueue  (    work_id bigint IDENTITY PRIMARY KEY,    status varchar(16) NOT NULL,    created_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    processed_at datetime2(0) NULL,    payload nvarchar(200) NULL  );  INSERT lab21.WorkQueue(status,payload)  SELECT TOP (5000)    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5 = 0 THEN 'READY' ELSE 'DONE' END,    CONCAT(N'work-',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;END;GOUSE ServiceHubOpsLab;GOSELECT name,state_desc,recovery_model_desc,page_verify_option_descFROM sys.databases WHERE name=DB_NAME();DBCC CHECKDB (N'ServiceHubOpsLab') WITH ESTIMATEONLY;GO

ESTIMATEONLY estimates tempdb space needed by consistency checking; it does not inspect the database for corruption. A clean estimate proves nothing about integrity. Likewise, seeing CHECKSUM as the page-verify option is useful defense-in-depth, but page checksums cannot replace CHECKDB.

2. PHYSICAL_ONLY and full CHECKDB answer different questions

PHYSICAL_ONLY limits checking to physical page/record-header and allocation consistency. Microsoft recommends it as a lower-overhead frequent check for large production databases, while still recommending periodic full CHECKDB. It skips important logical checks, including column-integrity checks, and cannot be combined with repair options. Therefore “PHYSICAL_ONLY passed” must not be recorded as “full logical integrity verified.”

DBCC CHECKDB (N'ServiceHubOpsLab')  WITH PHYSICAL_ONLY, NO_INFOMSGS;GODBCC CHECKDB (N'ServiceHubOpsLab')  WITH NO_INFOMSGS;GO-- Expected on a healthy lab:-- "DBCC execution completed. If DBCC printed error messages, contact your system administrator."-- No error rows should be emitted.

Permissions matter: current SQL Server documentation requires sysadmin or membership in the database db_owner role for CHECKDB. That is powerful. A production automation account should not receive broad rights merely because the integrity job was easier to configure that way. Decide whether a controlled Agent-owned job, signed module/runbook, restored-copy pipeline, or restricted operator role best fits the environment.

3. Restore-first is the recovery plan; repair is last-resort salvage

The deliberately wrong response to an integrity error is: “switch to SINGLE_USER and immediately run REPAIR_ALLOW_DATA_LOSS.” Microsoft explicitly warns that this option can lose more data than restoring a known-good backup. Repair can deallocate damaged rows/pages and does not preserve application-level constraints or business semantics. It is an emergency last resort when restore is impossible—not routine maintenance and not a shortcut around a broken backup strategy.

-- DO NOT execute this in the course lab or on production as routine maintenance.-- ALTER DATABASE ServiceHubOpsLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;-- DBCC CHECKDB (N'ServiceHubOpsLab', REPAIR_ALLOW_DATA_LOSS);-- ALTER DATABASE ServiceHubOpsLab SET MULTI_USER;-- Preferred incident sequence:-- 1) stop/limit writes if necessary;-- 2) preserve evidence and copies of files;-- 3) determine the last known-good restore point;-- 4) restore to a disposable target and run CHECKDB there;-- 5) validate constraints and application invariants;-- 6) decide controlled cutover/recovery.

If repair truly is the only remaining option, preserve physical file copies, understand the indicated repair level, run within a transaction where supported so results can be inspected, and then perform constraint/business validation. Replication, memory-optimized data and other features have additional caveats. The existence of a repair command does not reduce the need for tested backups.

4. Checking a restored copy reduces production interference and tests recovery

A strong operating pattern restores recent backup media to a separate validation instance or isolated database name and runs CHECKDB there. This simultaneously exercises backup readability, restore dependencies and database integrity. It still does not prove that the production source has not changed/corrupted since the backup, so many estates combine restored-copy validation with appropriately scoped checks on production.

The restored-copy strategy requires real capacity: backup media, restore destination, keys/certificates for encrypted backups, enough storage for the restored database and CHECKDB sparse snapshot, and enough time to finish within the recovery assurance window. Chapter 15’s restore sequence therefore becomes input to the Chapter 21 maintenance plan rather than a separate DBA ritual.

USE ServiceHubOpsLab;GODECLARE @started datetime2(0)=SYSUTCDATETIME();DBCC CHECKDB (N'ServiceHubOpsLab') WITH NO_INFOMSGS;DECLARE @finished datetime2(0)=SYSUTCDATETIME();INSERT lab21.RunAudit(run_name,started_at,finished_at,outcome,detail)VALUES(N'CHECKDB-FULL',@started,@finished,'SUCCEEDED',       CONCAT(N'duration_seconds=',DATEDIFF(second,@started,@finished)));SELECT TOP (5) * FROM lab21.RunAudit ORDER BY run_id DESC;GO

The audit row only records that the command completed without raising an error in this lab. A production wrapper should capture error number/message, target database, build, CHECKDB options, start/end UTC, restore source if applicable, and an immutable pointer to detailed output. Never fabricate a duration target from a small lab database.

5. Production scheduling is evidence-based

CHECKDB frequency depends on database size, change rate, storage risk, recovery objectives and the time required to discover corruption before good backup copies age out. “Every Sunday” is not a universal rule. For very large databases, frequent PHYSICAL_ONLY, periodic full CHECKDB, partition/filegroup strategies where appropriate, and restored-copy validation can be combined. Track actual runtimes and resource use, and test after engine/storage changes.

Do not stack CHECKDB, index rebuilds, backups, ETL and batch workloads into the same window simply because it is called “maintenance.” These tasks compete for I/O, CPU, memory, log bandwidth, tempdb, and HA/replication throughput. Chapter 21 treats the window as a capacity plan, not a calendar box.

Authoritative references

• DBCC CHECKDB (Transact-SQL)
• DBCC internal database snapshot usage
• DBCC CHECKTABLE (Transact-SQL)
• Backup and restore overview