DBAtlas · Database operations atlas

DBCC CHECKDB strategy: last good check, splitting the work, interpreting results

SQL ServerMaintenance

Command

-- Last successful CHECKDB (1900-01-01 = never):
SELECT name, DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS last_good_checkdb
FROM   sys.databases
WHERE  state_desc = N'ONLINE' AND name <> N'tempdb'
ORDER BY last_good_checkdb;

-- Estimated tempdb requirement:
DBCC CHECKDB (N'Musteri') WITH ESTIMATEONLY;

-- Split a large DB across days: allocation + catalog + table by table CHECKTABLE
USE [Musteri];
DBCC CHECKALLOC (N'Musteri') WITH NO_INFOMSGS;
DBCC CHECKCATALOG (N'Musteri') WITH NO_INFOMSGS;
DBCC CHECKTABLE (N'dbo.Siparis') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Get results as rows (error rows with object and page):
DBCC CHECKDB (N'Musteri') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS, MAXDOP = 4;

What it does

Shows the last successful CHECKDB time for every database, estimates the tempdb needed, lets you split the check for large databases into CHECKALLOC + CHECKCATALOG + CHECKTABLE pieces, and returns the results as rows (object, index, page).

When to use it

When auditing maintenance, for very large databases that do not fit in the maintenance window, or when determining the extent of corruption after 823/824.

Caution

The phrase "repair_allow_data_loss is the minimum repair level" in the result means repair will delete data; consider a restore from a good backup (or page restore) first. If only nonclustered indexes (index_id > 1) are affected, dropping and recreating the index fixes it without data loss. After finding corruption, investigate the disk subsystem. PHYSICAL_ONLY skips logical checks; a full CHECKDB is still required periodically. When splitting with CHECKTABLE, coverage is incomplete unless CHECKALLOC/CHECKCATALOG also run.

Open in DBAtlas →

Related