SQL Server is used widely for the management of database. It is possible that the data gets corrupt due to different reasons. Therefore, to deal with this issue SQL Server has a command, which act as a manual solution for corruption of the database. This command is referred as DBCC CHECKDB.

DBCC CHECKDB is a useful command often used to check the consistency error. This helps the users from high level of database corruption. The command proves to be useful when it comes to the information of physical as well as the logical integrity of the specified database objects.

DBCC CHECKDB command in SQL Server

database_name | database_id | 0

It is the ID or name of the database for which the checking is performed. In case, not specified or 0, then the current database is used. Name of the database must comply with the name of the rules for identifiers.

NOINDEX

Indicates checks nonclustered indexes for user tables should not be performed. This reduces the overall time of execution. It does not have any effect on the system because integrity checks are performed on system table indexes.

REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD

REPAIR_ALLOW_DATA_LOSS

This command tries for the repairing of all the errors that are reported. It is possible that you may loss some data, which is not repairable.

REPAIR_FAST

For backward compatibility maintains syntax. With this no recover actions are performed.

REPAIR_REBUILD

Repairs the data, which has no possibility of the loss of data. This includes quick repairs, like repairing absent rows in non-clustered indexes, and some time-consuming repairs, like rebuilding an index.

It is not possible to REPAIR_REBUILD, FILESTREAM data.

Note: Since DBCC CHECKDB with any of the Repair option is recoverable, therefore it is recommended to use CHECKDB

ALL_ERRORMSGS

Shows all reported errors per object by default. If the option is omitted and specified, it has no effect. Object ID sort all error, except messages that are generated from tempdb database.

EXTENDED_LOGICAL_CHECKS

If in SQL Server 2008, compatibility level is 100 or above, you need to do logical consistency checks on XML indexes, spatial indexes, and indexed view, where present.

NO_INFOMSGS

Restrains all informational messages.

TABLOCK

TABLOCK causes DBCC CHECKDB to run in a fast mode under heavily loaded database. However, it decreases the concurrency available on the database during the running period of DBCC CHECKDB.

ESTIMATEONLY

Provide the estimated space of tempdb space, which is required to run DBCC CHECKDB.

PHYSICAL_ONLY

Provides the checking of the integrity of the physical structure of the page. It record headers and the allocation consistency of SQL Server database. This check provides a small check of the physical consistency of the database. However, it can also expose checksum failures, common hardware failure, and torn pages.

With the help of PHYSICAL_ONLY option, the time period can be shorten to run DBCC CHECKDB on large databases. However, it is recommended to perform full DBCC CHECKDB command time to time.

PHYSICAL_ONLY does not allows any repair option and always implies NO_INFOMSGS

DATA_PURITY

Check the database for column values, which are out-of-range or invalid

Column-value integrity checks are enabled by default. It does not require the DATA_PURITY option. For those databases, which are upgraded to new versions of SQL Server, then by default, column-value checks are not enabled. Therefore to make them enable run DBCC CHECKDB WITH DATA_PURITY error free on the database. Once this is done, DBCC CHECKDB checks column-value integrity by default.

MAXDOP

When, on a system, the instance of SQL Server runs, which has more than one CPU or microprocessor, MAXDOP detects the best degree of parallelism, which shows the the number of processors used or involved to run one statement, for each parallel plan execution.

It overrides the maximums degree of parallelism

Note: if MAXDOP is set to zero, then the server will choose the maximum degree of parallelism.

Conclusion

In the above section, we have discussed the importance of DBCC CHECKDB command in SQL Server. We have also, learned the circumstances where the need of this command arises in front of the users.