Hi folks,

You might have seen my blog posts which was all about DBCC CHECKDB and how to repair the database using DBCC CHECKDB.

But I have tried to focus this blog on the various SQL Server DBCC Commands:

  • DBCC CHECKALLOC (‘database’):
    • A subset of DBCC CHECKDB that checks the allocation of all pages in the database.
    • The report is very detailed, i.e., listing the extent count (64 KB or 8 data pages) and data-page usage of every table and index in the database.
  • DBCC CHECKFILEGROUP (‘filegroup’):
    • It is very much similar to DBCC CHECKDB but specified to filegroup only.
  • DBCC CHECKTABLE (‘table’):
    • It performs physical and logical integrity checks on the table and all its non-clustered indexes.
  • DBCC CLEANTABLE (‘database’, “table”):
    • It reclaims space from a varchar, nvarchar, text, or ntext column that was dropped from the table.
  • DBCC CHECKCATALOG (‘database’):
    • It checks the integrity of the system tables within a database.
    • It also ensures referential integrity among tables, views, columns, and data types.
    • It will report any errors.
  • DBCC CHECKCONSTRAINTS (‘table’, ‘constraint’):
    • It examines the integrity of a specific constraint, or all constraint for a table.
    • It essentially generates and executes a query to verify each constraint, and reports any error found.
    • If no issues are being detected, nothing will be reported.
  • DBCC CHECKIDENT (‘table’):
    • It verifies the consistency of the current-identity column value and the identity column for a specific table.
    • If the problem exists, then the next value is updated to correct any error.
    • If there happens a case that the identity column is being broken, then the new identity value will violate a primary key or unique constraint, and new rows cannot be added to the table.
    • The following example will help you to understand this:


If you are executing a command DBCC CHECKDB, then you don’t need to issue DBCC CHECKALLOC,DBCC CHECKTABLE, and DBCC CHECKCATALOG separately, it will run along with it.

Well, this was all about DBCC Commands.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook