DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

·         Runs DBCC CHECKALLOC on the database.

·         Runs DBCC CHECKTABLE on every table and view in the database.

·         Runs DBCC CHECKCATALOG on the database.

·         Validates the contents of every indexed view in the database.

·         Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.

·         Validates the Service Broker data in the database.

DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.

Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

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 } ]

        }

    ]

]

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB.

-- Check the current database.

DBCC CHECKDB;

GO

-- Check the AdventureWorks2012 database without nonclustered indexes.

DBCC CHECKDB (AdventureWorks2012, NOINDEX);

GO

DBCC CHECKALLOC

Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC 

[

    ( database_name | database_id | 0 

      [ , NOINDEX 

      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

    )

    [ WITH 

        { 

          [ ALL_ERRORMSGS ]

          [ , NO_INFOMSGS ] 

          [ , TABLOCK ] 

          [ , ESTIMATEONLY ] 

        }

    ]

]

-- Check the current database.

DBCC CHECKCATALOG;

GO

-- Check the AdventureWorks2012 database.

DBCC CHECKCATALOG (AdventureWorks2012);

GO

DBCC CHECKCATALOG

Checks for catalog consistency within the specified database. The database must be online.

DBCC CHECKCATALOG 

[ 

    ( 

    database_name | database_id | 0

    )

]

    [ WITH NO_INFOMSGS ] 

-- Check the current database.

DBCC CHECKCATALOG;

GO

-- Check the AdventureWorks2012 database.

DBCC CHECKCATALOG (AdventureWorks2012);

GO

DBCC CHECKTABLE

Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKTABLE 

(

    table_name | view_name

    [ , { NOINDEX | index_id }

     |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } 

    ] 

)

    [ WITH 

        { ALL_ERRORMSGS ]

          [ , EXTENDED_LOGICAL_CHECKS ] 

          [ , NO_INFOMSGS ]

          [ , TABLOCK ] 

          [ , ESTIMATEONLY ] 

          [ , { PHYSICAL_ONLY | DATA_PURITY } ] 

        }

    ]

To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

For the specified table, DBCC CHECKTABLE checks for the following:

• Index, in-row, LOB, and row-overflow data pages are correctly linked.
• Indexes are in their correct sort order.
• Pointers are consistent.
• The data on each page is reasonable, included computed columns.
• Page offsets are reasonable.
• Every row in the base table has a matching row in each nonclustered index, and vice-versa.
• Every row in a partitioned table or index is in the correct partition.
• Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

Result set:

DBCC results for 'HumanResources.Employee'.

There are 288 rows in 13 pages for object 'Employee'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

https://msdn.microsoft.com/en-us/library/ms176064.aspx