If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that refer­ence the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)

There is one concept about this command that I did not know about until recently, intrigued? Read on.

I go through my offset calculations and proceed to corrupt a specific table.

USE PartyWorks
GO
SELECT * FROM [person].[EmailAddress]

CHECKDB returns the following: CHECKDB found 0 allocation errors and 4 consistency errorsin database ‘PartyWorks’ repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PartyWorks).

What does CHECKTABLE say?

DBCC CHECKTABLE (N'Person.EmailAddress') WITH NO_INFOMSGS

CHECKTABLE found 0 allocation errors and 4 consistency errors in table ‘Person.EmailAddress’ (object ID 1189579276) repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (PartyWorks.Person.EmailAddress).

Whilst running the command it creates a database snapshot too, as expected.

The point of this post? You can actually run repair via CHECKTABLE…as the message clearly states. I always thought that you needed to run repair via CHECKDB only. Well I was wrong.

DBCC CHECKTABLE ('Person.EmailAddress', REPAIR_ALLOW_DATA_LOSS)

I re-run the command for one last sanity check.

DBCC CHECKTABLE (N'Person.EmailAddress')

DBCC results for ‘Person.EmailAddress’. There are 19889 rows in 249 pages for object “Person.EmailAddress”. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Filed under: Admin, CHECKDB, Corruption, SQL SERVER Tagged: Corruption, DBCC CHECKDB, DBCC CHECKTABLE, SQL server, TSQL