DBCC

DBCC CHECKDB

DBCC CHECKDB – is the Database Console Command, and is very important to SQL Server. This command is used to check the physical and logical consistency of a database.

DBCC is Used To:

• Check for and fix database corruption issues.

• Checks each table one by one for data corruption issues.

• Checks values within tables to ensure that’s values are valid for that data type.

• Should be run regular.

• Should be run off hours

• Most expensive IO operations.

---

DBCC Internals:

When this command is issued it does run few commands that actually do the validation and do check if the pages are consistent and report if they are not. Basically, DBCC CHECKDB internally does below:

DBCC CHECKALLOC 

This check validates the disk space allocation and compares it with the internal structure of the pages, it can also repair the allocation structure if an anomaly is found. It runs at a database level.

DBCC CHECKTABLE 

This check integrity of the pages and structure for the table or indexed views, it also validates linkages between pages, sort orders, pointers and offset. This check runs at a table and view level.

DBCC CHECKCATALOG 

This check validates the consistency for data type used in the database and compares each of them with the systypes and sysobjects system table. This check runs at a database level.

---

Repair Levels

To repair a suspect database use following script:
DBCC CHECKDB(databaseName, repair options)

• REPAIR_REBUILD
• REPAIR_FAST
• REPAIR_ALLOW_DATA_LOSS

1. Repair_Rebuild

If a user wants to repair SQL database without any loss of information, then this repair option is selected.

2.Repair_Allow_Data_Loss:

The database should be set on single user mode to run REPAIR_ALLOW_DATA_LOSS. In some cases users take help of repair_allow_data_loss command to repair all type of errors in the SQL database. It is pretty much clear from the name that there might be some loss of data during the recovery process. This means this DBCC CHECKDB command does not promise data integrity.

3. REPAIR_FAST: 

Does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes.

---