DBCC CHECKDB on very large SQL Server databases: PHYSICAL_ONLY, split checks, offloading to a restored copy and tracking the last good check

#The goal

A full DBCC CHECKDB on a multi-terabyte database now runs for longer than the maintenance window, competes with the overnight batch, or fills the disk with its internal snapshot. The answer is not to stop checking. It is to split the work so that every page and every logical structure still gets checked within a known period, and you can prove when it last happened.

This runbook gives a weekly plan, the commands for each piece, and the monitoring that tells you when a database has gone too long without a clean check.

#What each option actually checks

CHECKDB is effectively CHECKALLOC + CHECKCATALOG + CHECKTABLE on every table (plus a few extras such as indexed views and Service Broker). So running CHECKALLOC and CHECKCATALOG plus CHECKTABLE on every table over a cycle covers the same logical ground in smaller pieces.

Warning

PHYSICAL_ONLY alone does not catch logical corruption such as a nonclustered index that disagrees with its base table. Use it as the frequent check on production, never as the only check.

#Step 1: measure before you plan

Find the size and history of each database:

SELECT d.name,
       CAST(SUM(mf.size) * 8 / 1024.0 / 1024 AS decimal(10,1)) AS size_gb,
       DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS last_good_checkdb
FROM sys.databases d
JOIN sys.master_files mf ON mf.database_id = d.database_id AND mf.type = 0
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY size_gb DESC;

LastGoodCheckDbTime is available from SQL Server 2016 SP2. On older builds, read dbi_dbccLastKnownGood from DBCC DBINFO ('Sales') WITH TABLERESULTS.

Ask how much tempdb a full check would need:

DBCC CHECKDB (N'Sales') WITH ESTIMATEONLY, NO_INFOMSGS;

While a check runs, watch progress:

SELECT session_id, command, percent_complete,
       DATEADD(ms, estimated_completion_time, SYSDATETIME()) AS eta,
       wait_type, total_elapsed_time / 60000 AS elapsed_min
FROM sys.dm_exec_requests
WHERE command LIKE 'DBCC%';

Run one full CHECKDB on a restored copy (step 4) to get a realistic duration before you decide how to split the work.

#Step 2: daily PHYSICAL_ONLY on production

DBCC CHECKDB (N'Sales') WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS, MAXDOP = 4;

MAXDOP on DBCC CHECKDB caps parallelism so the check does not take every core during business hours (SQL Server 2014 SP2 and later). Pick a value that leaves room for the workload.

CHECKDB runs against an internal database snapshot stored as sparse files next to the data files. On a busy database that snapshot grows with every page changed during the check. If the data volume is nearly full, the snapshot can fail and the check stops with an error.

Caution

Do not switch to WITH TABLOCK to avoid the snapshot on a production database. It takes locks that block the application for the duration of the check. Free disk space, or move the full check to a restored copy instead.

#Step 3: spread logical checks across the week

Run the light checks weekly, and CHECKTABLE on a slice of tables each night so every table is covered once per cycle. A simple, deterministic split is by object_id modulo the number of nights:

DECLARE @nights int = 7,
        @tonight int = (DATEPART(weekday, SYSDATETIME()) - 1);  -- 0..6
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'DBCC CHECKTABLE (N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
             + N''') WITH NO_INFOMSGS, ALL_ERRORMSGS;' + CHAR(10)
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0
  AND t.object_id % @nights = @tonight
ORDER BY t.object_id;

PRINT @sql;           -- review first
EXEC sys.sp_executesql @sql;

Run this in the target database. On Sunday, add the database-wide pieces:

DBCC CHECKALLOC (N'Sales') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKCATALOG (N'Sales') WITH NO_INFOMSGS;

A modulo split balances table count, not size. If one table is half the database, give it its own night, or check it on the restored copy only.

If you use Ola Hallengren's maintenance solution, DatabaseIntegrityCheck already supports this pattern with @CheckCommands (CHECKDB, CHECKFILEGROUP, CHECKTABLE, CHECKALLOC, CHECKCATALOG), @PhysicalOnly and @TimeLimit, and it resumes with the objects it has not checked recently. Check the parameter list for your installed version:

EXECUTE dbo.DatabaseIntegrityCheck
    @Databases     = 'Sales',
    @CheckCommands = 'CHECKTABLE',
    @TimeLimit     = 14400,      -- stop starting new objects after 4 hours
    @LogToTable    = 'Y';

#Step 4: full CHECKDB on a restored copy

Restoring last night's full backup to another server and running a full CHECKDB there does two jobs at once: it proves the backup restores, and it checks every logical structure without touching production.

RESTORE DATABASE Sales_Verify
FROM URL = N'https://backupsacct.blob.core.windows.net/sql/Sales_FULL.bak'
WITH MOVE N'Sales'     TO N'E:\Verify\Sales_Verify.mdf',
     MOVE N'Sales_log' TO N'F:\Verify\Sales_Verify.ldf',
     REPLACE, STATS = 5;

DBCC CHECKDB (N'Sales_Verify') WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS;

Logical file names and backup location vary; read them with RESTORE FILELISTONLY first. EXTENDED_LOGICAL_CHECKS adds checks on indexed views, XML and spatial indexes; drop it if the run is too long.

dbatools can automate restore-and-check across many databases:

Test-DbaLastBackup -SqlInstance sql-prod-01 -Destination sql-verify-01 `
    -Database Sales | Format-Table Database, RestoreResult, DbccResult, RestoreElapsed, DbccElapsed

Warning

A clean check on the copy proves the backup was clean, not that production storage is healthy today. Pages that went bad on production after the backup are caught by the daily PHYSICAL_ONLY, by backups taken WITH CHECKSUM, and by alerts on errors 823, 824 and 825. Keep all of them.

#Availability Groups

Each replica has its own copy of the data files on its own storage. A clean check on a secondary says nothing about the primary's disks, and the other way round. Run at least PHYSICAL_ONLY on every replica, and the full check on the replica you take backups from (or on a restored copy of those backups). LastGoodCheckDbTime is tracked per replica.

On Azure SQL Database, Microsoft runs integrity checks for you. On Azure SQL Managed Instance you can and should run your own checks; plan them like a large on-premises database.

#Step 5: alert on the last good check

Schedule this as a SQL Agent job step and fail the step (so the job's failure notification fires) when any database is overdue:

DECLARE @maxDays int = 8;

IF EXISTS (
    SELECT 1 FROM sys.databases d
    WHERE d.state_desc = 'ONLINE' AND d.database_id <> 2
      AND ISNULL(DATEDIFF(day, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime2),
                          SYSDATETIME()), 9999) > @maxDays)
    RAISERROR('One or more databases have no clean CHECKDB in the last %d days.', 16, 1, @maxDays);

LastGoodCheckDbTime is only updated by a clean CHECKDB (including PHYSICAL_ONLY), not by CHECKTABLE slices. If you rely on split checks, log their results to a table (Ola's CommandLog does this) and alert from there instead.

#A sample weekly plan

#Prevent it next time

• Set PAGE_VERIFY CHECKSUM on every database and back up WITH CHECKSUM.
• Re-measure check duration quarterly; databases grow and yesterday's split stops fitting.
• Keep free space on data volumes for the CHECKDB snapshot, and alert before it runs out.
• Record the plan per database (what runs where, and the maximum age of a clean check) in the platform standard, so a new large database gets it on day one.