Is there any specific differences on table level integrity check between DBCC CHECKDB and DBCC CHECKTABLE

dbcc-checkdbdbcc-checktablesql serversql-server-2016

I've faced with allocation error in one of our databases while running DBCC CHECKDB on it. It throws below-mentioned error:

Msg 8947, Level 16, State 1, Line 5
Table error: Multiple IAM pages for object ID 1277199566, index ID 1, partition ID 72057717676669456, alloc unit ID 72057708766647328 (type LOB data) contain allocations for the same interval. IAM pages (1:425664) and (1:1422669).

CHECKDB found 1 allocation errors and 0 consistency errors in table 'schemaName.tableName' (object ID 1277199566).
CHECKDB found 1 allocation errors and 0 consistency errors in database 'databaseName'.

Weird thing is that, when I'm trying to run DBCC CHECKTABLE on this table, it doesn't show any error. Additionally, I've tried to run CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS option, and I didn't get any error.

Per Microsoft documentation:

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

For the specified table, DBCC CHECKTABLE checks for the following:

  • Index, in-row, LOB, and row-overflow data pages are correctly linked.
  • Indexes are in their correct sort order.
  • Pointers are consistent.
  • The data on each page is reasonable, included computed columns.
  • Page offsets are reasonable.
  • Every row in the base table has a matching row in each nonclustered index, and vice-versa.
  • Every row in a partitioned table or index is in the correct partition.
  • Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM

But as I can see, its not true for opposite. We can't run CHECKTABLE for just one table and get the same result for the same table.

Does anyone have information related to differences between these two commands, in terms of checking integrity and consistency on specific object?

Best Answer

DBCC CHECKDB:

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

  • Runs DBCC CHECKALLOC on the database.
  • Runs DBCC CHECKTABLE on every table and view in the database.
  • Runs DBCC CHECKCATALOG on the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database.

Running DBCC CHECKTABLE is only one of those steps.

To see the allocation errors, you would need to run DBCC CHECKALLOC.

DBCC CHECKTABLE does perform extensive checks on the pages belonging to that object. It does not check database-wide allocation metadata that might refer to the table in an inconsistent way. DBCC CHECKALLOC performs those checks.

Related Question