SQL Server DBCC Commands

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:
USE AdventureWorks2008R2
DBCC CHECKIDENT('HumanResources.Department')

1_SQL_Server_DBCC_Commands

   

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!!

 

Regards

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

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server DBCC Commands”

  1. hi,

    i found this post to be pretty much helpful, but plz add some more commands and examples as well for better understanding. 🙂

  2. What does one do if theyb have a database they can’t attach to SQL server. I have such a problem and when i try to attach it I get an error. So how can I fix/repair the DB so that I can attach it. Your instructions above seem to work only if the DB is already attached to SQL.

  3. Hi Jerry,

    What error are you getting?

    Incase you want to attach this DB and let SQL do a auto recovery on this DB follow the below mentioned steps:

    1. create a new DB with the same name on your SQL instance.
    2. Stop SQL Services
    3. Replace the physical files of newly created DB with the files you want to attach
    4. Start SQL Services
    5. Let SQL do the auto-recovery and monitor the progress via – Error Log.

    Do let us know the status.

Leave a Reply

Your email address will not be published.