How to Use DBCC CHECKDB in SQL Server

Use: By far the most widely used command to check the status of your database is DBCC CHECKDB. This statement checks the allocation and structural integrity of all data and index pages for each table and indexed view, as well as text and image objects. DBCC CHECKDB ensures that all data and index pages are correctly linked and pointers are consistent. Both user and system tables are checked within the specified database. DBCC CHECKDB executes both DBCC CHECKALLOC as well as DBCC CHECKTABLE for every object in the database

Let’s take a look at a couple of the more common options to know how to use dbcc checkdb in sql server.

Steps to execute the DBCC CHECKDB:

1. The first option on the command looks like this for the CanonECFG5 database:

USE MASTER;

GO

DBCC CHECKDB ('TestDB', NOINDEX) WITH NO_INFOMSGS;

 The command above checks the CanonECFG5 database but not its indexes. This won’t take long at all. The output returned will tell you if there are problems with the database. If so, check to make sure your backup is handy and then you can run the next level of this command.

2. In this case we don’t have any errors. But if find any errors in execution, execute the next level of DBCC command as below.

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_FAST) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;

This command will attempt to fix many errors, but won’t allow any data to be lost. If that doesn’t work, the next level of the command is in step 3.

3. Execute the below DBCC command

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_REBUILD) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;
GO

This command takes longer, but will also correct the indexes (if it can). It will also not allow data loss. Should this command not correct your errors, you’ll definitely want to have that backup handy, because you’re going to need it. The next level of this command will potentially lose data.

4. Now execute the below DBCC command as a last and final repair trail.

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;

As you can probably guess, this command could potentially lose data or make your applications unusable.

Info:

Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

 **Note: If we want to repair the database the db should be in single user mode.

 Q. How to know the estimated TempDB space required to run CHECKDB command?

Ans:

Below command calculates the amount of space in “tempdb” required to run DBCC CHECKDB statement. However, the actual statement isn’t executed.

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, ESTIMATEONLY

 Sample Output:

Estimated TEMPDB space (in KB) needed for CHECKDB on database TestDB = 14254.

 Q. How to fast up the DBCC check db execution process?

Ans:

The below command forces to obtain an exclusive lock on database which makes bit faster the process.

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, TABLOCK

 Q. What is Phycial_Only option in DBCC CHECKDB?

Ans:

This command limits the checking to the integrity of the physical structure of the page and record headers, but it can also detect torn pages, checksum failures, and common hardware failures. Using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY

Q. How to check data purity using DBCC CHECKDB?

Ans:

Below command causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; limits the checking to the integrity of the physical structure of the page and record

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, DATA_PURITY

Please check here for more DBCC commands.