SQL Server DBCC CHECKDB: An Overview

Andrew Jackson | October 10th, 2017 | SQL |

Database management is very essential for the establishment of the successful business. SQL Server plays a very significant role in this field. Most of the organizations use it for keeping their data appropriately. It has various features that help to retain the data security. One of these inbuilt features is DBCC CHECKDB command. Let us take some basic knowledge about the same in this blog.

Introduction of SQL Server DBCC CHECKDB

It is useful in checking the logical as well as physical integrity of all objects in indicated database by executing the below-mentioned operations:

  • Executing DBCC CHECKALLOC on database helps to verify the consistency of the disk- space distribution structures for indicated database.
  • Running DBCC CHECKTABLE on each table and see in database is helpful to check the integrity of pages and structures, which make up table/ indexed view.
  • DBCC CHECKCATALOG on SQL Server database can check the consistency of catalog in the database.
  • It validates or authenticates the content of each indexed view in database. Moreover, it also authenticates the link-level uniformity between the table metadata and the directories of file system and file at the time-saving varbinary (max) data in file system via FILESTREAMS.
  • Apart from this, it also validates Server Broker data in SQL Server database.

Advantage of SQL Server DBCC CHECKDB

Corruption in the database is one of the common scenarios nowadays. It may result in wrong data outcomes, failure of SQL statements, and in fact, can take down complete SQL instance. DBCC CHECKDB makes you aware or warns you about the corruption or damage so that you rectify it as early as possible and prevent harmful effects of it.

Method to use SQL Server DBCC CHECKDB

It does not have a complicated structure to handle. You can use it easily without anyone assistance. There are some options, you can access with the statement. If we talk about the basic syntax, then it looks like this:

DBCC CHECKDB (“DatabaseName”)

Options with SQL Server DBCC CHECKDB: These are the options with DBCC CHECKDB:

NOINDEX: It indicates that rigorous check of the nonclustered indexes for the user tables should be avoided. This lessens the complete execution time. This option does not affect the tables of the system as integrity checks are executed on system table indexes.

DATA_PURITY: It redirects DBCC CHECKDB to verify the database for column values, which are invalid/ out-of-range. Let us take an example. DBCC CHECKDB finds the columns along with time and data values, which are larger than/ less than the suitable range for datetime data type; or approximate-numeric or decimal data type columns along with precision or scale values that are invalid.

PHYSICAL_ONLY: It restricts the checking to integrity of physical layout of page and records the headers and allocation consistency of database. PHYSICAL_ONLY check is developed for giving a little overhead check of physical consistency of SQL database. However, it can even detect torn pages, common hardware failure, and checksum failures that can consist of the data of users.

TABLOCK: This option causes DBCC CHECKDB command to get locks instead of accessing a snapshot of internal database. It contains a short-term exclusive lock on database. Moreover, TABLOCK will direct DBCC CHECKDB to run quickly on SQL database under the heave load. However, lessens the concurrency obtainable on database during DBCC CHECKDB is in running mode.

NO_INFOMSGS: It suppresses all the information messages.

Solution to Repair SQL Server Database

In case, you do not have the backup, it is suggested to use DBCC CHECKDB with the option of repair. These are some repair options, which are available to access:

REPAIR_ALLOW_DATA_LOSS: This command tries to repair all the reported errors. However, these repairs may loss some your data.

REPAIR_REBUILD: It executes the repair, which have no possibility of the loss of data. It can include instant repairs like repairing the missing rows in a non-clustered index, & more time- taking repairs like rebuilding an index.

As I have mentioned above, that backup of data is very essential in recovery process. However, if you do not have the backup then apart from these commands, you can opt for third party tool to repair SQL Server database. It is developed to repair the SQL Server data files without any data loss. The software is reliable and can be used without any trouble.

Wrapping It Up

We know the significance of SQL Server for a company to manage their data. Therefore, in the above blog, we have learned a very crucial command i.e. DBCC CHECKDB. This help user to keep their database on SQL Server protected. In case of corruption, they can use this command to fix it. However, users can even opt for a third party tool to prevent any kind of data loss.

Transaction Log Restore