The maintenance window is four hours, but the integrity check needs ten. Splitting DBCC CHECKDB work across the week gives every table a scheduled turn and leaves a record of what actually ran. The schedule needs careful coverage because an incomplete check can look reassuring.
Know What Splitting DBCC CHECKDB Covers
DBCC CHECKDB includes allocation, catalog, and table checks. Running CHECKALLOC, CHECKCATALOG, and CHECKTABLE across separate windows can cover much of the same ground. It is still not identical to a single full CHECKDB at one point in time. Cross-object checks and a consistent database-wide snapshot are not recreated by seven independent nights. I prefer a complete CHECKDB on a restored copy when the production window cannot hold it. The split schedule is still useful for early warning on the live database.
Start by measuring a full check on a representative restore. Note the elapsed time, tempdb space, and I/O load. The goal is a plan whose coverage you can prove, not a plan that looks tidy on a calendar. What evidence will tell you that every table was checked this week?
Build a Seven-Night Table Roster
Use a stable roster of user tables, including schema and object ID. Assign each table to one of seven buckets. A simple count-balanced list is a starting point, not a runtime-balanced schedule. One huge table can take longer than fifty small ones. After the first week, move heavy tables to quieter nights. Keep the roster under review because deployments add and drop objects.
CREATE TABLE dbo.IntegrityTableSchedule
(
object_id int NOT NULL PRIMARY KEY,
night_number tinyint NOT NULL
CHECK (night_number BETWEEN 0 AND 6)
);
INSERT dbo.IntegrityTableSchedule (object_id, night_number)
SELECT t.object_id,
CONVERT(tinyint,
(ROW_NUMBER() OVER (ORDER BY s.name, t.name) - 1) % 7)
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0;Create this in the database being checked, or place the schedule in an administration database and include database_id. Do not run the CREATE twice unchanged. A deployment process should compare sys.tables with the roster and assign new objects before the next night's job. Temporal history tables, partitioned tables, and tables with unusual storage still belong in the inventory.
Log Every CHECKTABLE Attempt
A job step that says it succeeded is too coarse. Record the table, command, start time, finish time, and result for each attempt. Also retain SQL Agent output or a job output file, since DBCC can report important detail beyond one status flag. The following table gives the nightly script a durable log. A failed check is never marked complete merely because its job reached the last line.
CREATE TABLE dbo.IntegrityRunLog
(
run_id bigint IDENTITY(1,1) PRIMARY KEY,
object_id int NULL,
check_name nvarchar(30) NOT NULL,
started_at datetime2(0) NOT NULL,
finished_at datetime2(0) NULL,
succeeded bit NULL,
error_message nvarchar(4000) NULL
);The job can read the roster for the assigned night and build each two-part table name with QUOTENAME. It then runs DBCC CHECKTABLE and inserts a log row in TRY and CATCH. Make the job fail at the end when any check fails, so monitoring notices it. A cursor is reasonable here because DBCC accepts one target at a time and the work is administrative. This is one place where a row-by-row loop is a feature, not a confession.
Add Allocation and Catalog Checks
Schedule DBCC CHECKALLOC and DBCC CHECKCATALOG at least once in the weekly cycle. Log each as its own run, including start and finish. Do not hide them inside a job that only logs table names. If CHECKALLOC finds damage, pause normal maintenance and follow your corruption response plan. Repair options can lose data. Restore testing matters more than a clever schedule.
DBCC CHECKALLOC (N'YourDatabase') WITH NO_INFOMSGS;
DBCC CHECKCATALOG (N'YourDatabase') WITH NO_INFOMSGS;Replace the database name. Run in the correct security context and save the complete output. These commands still consume I/O and space, so measure their runtime before choosing the night. Do not schedule CHECKALLOC on the same busy evening as the largest CHECKTABLE batch just because the calendar has an empty box.
Use PHYSICAL_ONLY as a Broad Weekly Pass
DBCC CHECKDB WITH PHYSICAL_ONLY checks physical structure with less work than the full logical check. It is valuable for a large database, but it does not perform every logical check. Put it on a predictable weekly night and retain the result. Then continue the table, allocation, and catalog rotation. The physical pass is a complement to the split schedule and to periodic full checks on a restore.
DBCC CHECKDB (N'YourDatabase')
WITH PHYSICAL_ONLY, NO_INFOMSGS;Do not treat an empty Messages tab as the entire record. Capture the job output, completion time, and SQL Server error log messages. I have seen maintenance plans that looked green because the operator read only the job status, while the last meaningful integrity check was months old. The calendar deserves evidence.
Prove Weekly Coverage When Splitting DBCC CHECKDB
At the end of each week, join the current table roster to successful CHECKTABLE rows from the last seven days. Flag missing tables and failed attempts. Compare the roster against sys.tables again, since a new table will not appear in yesterday's static schedule. Also check for successful CHECKALLOC, CHECKCATALOG, and PHYSICAL_ONLY runs in that period. A missed night is a gap to reschedule, not a box to color green.
SELECT s.object_id, OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
MAX(l.finished_at) AS last_good_check
FROM dbo.IntegrityTableSchedule AS s
LEFT JOIN dbo.IntegrityRunLog AS l
ON l.object_id = s.object_id
AND l.check_name = N'CHECKTABLE'
AND l.succeeded = 1
AND l.finished_at >= DATEADD(day, -7, SYSDATETIME())
GROUP BY s.object_id
HAVING MAX(l.finished_at) IS NULL;The seven-day window rolls, so run the audit at a consistent time after all scheduled nights. Keep a separate report for failures; a later success should not erase the fact that a check failed earlier. If a table cannot finish in its assigned window, split the workload around it and test the plan. Do not silently omit the hard table. That is usually the one you most want examined.
Splitting DBCC CHECKDB Still Needs a Full-Check Path
A restored copy lets you run full CHECKDB without forcing production into a long maintenance window. Restore the latest backup chain regularly, check it, and record the restore and integrity results together. This also proves the backup can recover. If the restore copy is too small for a full check, plan capacity rather than claiming the weekly split is equivalent.
Review the schedule after large loads, partition changes, and upgrades. Integrity checking is a habit with a measurable finish line: every object checked, every failure reviewed, and a full-check path documented. I would rather show one honest coverage gap than a perfect dashboard built from missing rows.
Related reading on this blog: Why Suddenly DBCC CHECKDB Running Very Slow? and DBCC CHECKDB WITH PHYSICAL_ONLY Failing and DBCC CHECKDB Succeeding: Bug.
A split integrity check is not a shortcut, it is a schedule whose full coverage must be proved.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
