SQL Server – DBCC CHECKDB

Hello Folks,

You might have heard about DBCC. If not, I am going to give you some heads up:

DBCC Commands stands for Database Console Commands. They are being used for:

• Checking database integrity
• Performing Maintenance Operations on databases, tables, indexes, and filegroups
• Collecting and displaying information during troubleshooting issues

The main function of DBCC CHECKDB is to check the logical and physical integrity of all the objects in the specified database by performing the following operations:

• It runs DBCC CHECKALLOC on the database.
• It runs DBCC CHECKTABLE on every table and view in the database.
• It runs DBCC CHECKCATALOG on the database.
• It validates the contents of every indexed view in the database.
• It validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• It validates the Service Broker data in the database.

The Syntax for the DBCC CHECKDB is:

DBCC CHECKDB 
[
    [ ( database_name | database_id | 0
        [ , NOINDEX 
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH 
        {
            [ ALL_ERRORMSGS ]
            [ , EXTENDED_LOGICAL_CHECKS ] 
            [ , NO_INFOMSGS ]
            [ , TABLOCK ]
            [ , ESTIMATEONLY ]
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]
        }
    ]
]

DBCC execution completed. If DBCC printed error messages, contact your System Administrator.

DBCC CHECKDB also checks things like:

• Index Pointers
• Data-Page Offsets
• Linking between data pages and index pages
• Structural content of the data and index pages

If there’s happen a condition where a data page is being left half-written, then DBCC CHECKDB is the best means of detecting the problem.

Now, to check the integrity of the database, issue the following command:

Well, this was a brief about DBCC CHECKDB.

If you liked this post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

And also comments on this!!

Regards

Piyush Bajaj

@piyushbajaj007

piyush.bajaj.2007@gmail.com

www.sqlservergeeks.com/blogs/piyush.bajaj.2007