SQL Server – DBCC CHECKTABLE

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.

dbfiles

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)

checkedTable
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.

2 thoughts on “SQL Server – DBCC CHECKTABLE

  1. Pingback: Dew Drop - November 21, 2017 (#2608) - Morning Dew

  2. Pingback: Running DBCC CHECKTABLE – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s