Wednesday, June 22, 2011

How to check integrity of SQL server 2005 database by DBCC CHECKDB?

In Microsoft SQL server 2005, a database administrator can check the physical and logical integrity of all database objects in a specified database by performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG operation on the database. DBCC CHECKALLOC and DBCC CHECKCATALOC commands run on the database while DBCC CHECKTABLE command runs on the database's table.

DBCC CHECKALLOC command: Run this command for checking the consistency of disk space structure of the desired database.

DBCC CHECKTABLE command:  Run this command on the specified database's table for checking the integrity of all pages that make up the table.

DBCC CHECKCATALOC command: Run this on the specified database for checking the catalog consistency.

You can run all these commands along with DBCC CHECKDB command. This means that you will have not run all these command separately.

Note: If you wish to run all these commands separately then syntax will be differ.

Syntax for running these commands with DBCC CHECKDB:

    [ ( Your_database_name | database_id | 0
        [ , NOINDEX
        ) ]
    [ WITH
            [ ALL_ERRORMSGS ]
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]

Take step by step look on the syntax:

Your_database_name | database_id | 0: You can specify your database name or database id. If you have not mention the database name or id then default database is current database.

NOINDEX: You can specify this for intensive checks of non clustered index.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD: First set specified database in single user mode and specify this for repair SQL server database object. You may lose some amount of data.

ALL_ERRORMSGS: This statement displays all reported errors message per object.

NO_INFOMSGS: Displays the all information messages list.

TABLOCK: It will allow the DBCC CHECKDB to run fast.

ESTIMATEONLY: Displays the estimated amount space of tempdb.

PHYSICAL_ONLY: You can specify the limit for checking the physical structure of the database.

DATA_PURITY: This will check the values in the database columns.

1 comment:

  1. I'm so glad to have found something informative in your site. Thanks for sharing.