Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The DBCC CHECKDB command is used to detect and repair logical and physical errors that occur in the database. When you run the DBCC CHECKDB command on the database, you do not have to run DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG commands separately. Because DBCC CHECKDB contains all of them. You can find details about these commands in the following articles.
“DBCC CHECKALLOC Command On SQL Server“,
“DBCC CHECKTABLE Command On SQL Server“,
“DBCC CHECKCATALOG Command On SQL Server”
This command(DBCC CHECKDB) is very useful if the database is suspect. We also need to run this command regularly on each database. To run this command regularly, you can read the article “SQL Server Maintenance“.
NOTE: If there is not enough space on the disks that contain your database files, your DBCC CHECKDB job will get an error as follows.
The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001954fac000 in file ‘.mdf_MSSQL_DBCC37’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
DBCC CHECKDB has several different uses. Let’s examine them all one by one:
Consistency Check:
The following command detects logical and physical errors in the Test database.
1 | DBCC CHECKDB ('Test') |
Consistency Check With NOINDEX:
With the help of the following query, errors other than non clustered indexes in the user tables are detected.
1 | DBCC CHECKDB (N'Test',NOINDEX) |
Correct Errors With REPAIR_REBUILD:
You can run the DBCC CHECKDB command to correct errors in the non-clustereded indexes in the user tables by using the following command without any loss of data.
The REPAIR_REBUILD operation does not fix any errors that contain filestream data.
1 2 3 4 5 | ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB (N'Test',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO ALTER DATABASE Test SET MULTI_USER; |
Correct Errors With REPAIR_ALLOW_DATA_LOSS:
You can run the DBCC CHECKDB command to correct all errors in the database with the risk of data loss using the query below.
1 2 3 4 5 6 7 | ALTER DATABASE [Test] SET EMERGENCY; GO ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB (N'Test', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO ALTER DATABASE [Test] SET MULTI_USER; |
Even if you don’t write ALL_ERRORMSGS at the end of the script, this is the default value. It means to show all errors received for each object.
NO_INFOMSGS at the end of the script means that it does not display informational messages.
NOTE1: After you make sure that the database is suspect, the first thing we need to do before we start the attempt to recover is to get a backup of the suspect database !!!
NOTE2: After running DBCC CHECKDB with REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS, I recommend that you run the DBCC CHECKCONSTRAINTS command. You can find information about this command in my article “DBCC CHECKCONSTRAINTS Command On SQL Server”
Logical Consistency Check For Indexed view, XML index, and Spatial Indexes:
By default, the DBCC CHECKDB command only tests the physical consistency for indexed view, XML index, and spatial indexes.
You can also use the following command to check the logical consistency for indexed view, XML index, and spatial indexes in tables in the databases with compatibility level SQL Server 2008 and later. You may want to read my article “What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level“.
1 | DBCC CHECKDB (N'Test') WITH EXTENDED_LOGICAL_CHECKS,NO_INFOMSGS, ALL_ERRORMSGS; |
The DBCC CHECKTABLE command uses internal database snapshot. This means that there is no blocking when you run this command.
You may want to read the article named “What is Database Snapshot On SQL Server”
Consistency Check With Lock:
If you run using the following command, this command puts the exclusive lock on the database instead of using the internal database snapshot. You may want to read my article “SQL Server Lock Types“.
1 | DBCC CHECKDB (N'Test') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS; |
Calculate Necessary Space For Consistency Check:
If you use the following command, no consistency check is performed. Only the amount of space required in tempdb is calculated so that a consistency test can be performed, and it produces an output as follows.
1 2 | DBCC CHECKDB (N'Test') WITH ESTIMATEONLY,NO_INFOMSGS, ALL_ERRORMSGS; Estimated TEMPDB space (in KB) needed for CHECKDB on database Test = 756. |
Only PHYSICAL Consistency Check:
When you run it using the command below, only physical consistency check is performed. It is not done logically. You can run this way when you want to run the DBCC CHECKDB command frequently, but you must run DBCC CHECKDB at regular intervals without the PHYSICAL_ONLY command. When you run it with the PHYSICAL_ONLY command, it is completed faster and using less cpu.
If you ask me, using this command doesn’t make much sense. Because it doesn’t control its logical consistency.
With the PHYSICAL_ONLY command, you cannot perform any REPAIR operation. Also, when you use PHYSICAL_ONLY, the file stream data is not checked. You can find information about File Stream in my article “What is File Stream On SQL Server“.
1 | DBCC CHECKDB (N'Test') WITH PHYSICAL_ONLY,NO_INFOMSGS, ALL_ERRORMSGS; |
DATA_PURITY Option:
When you run it using the command below, it is checked whether the values of the columns in the tables in the database are compatible with the data type of the column. If your database is SQL Server 2005 or higher, the column values are checked automatically and no DATA_PURITY option is required.
1 | DBCC CHECKDB (N'Test') WITH DATA_PURITY,NO_INFOMSGS, ALL_ERRORMSGS; |
The DBCC CHECKDB command does not check the disabled indexes.
For faster completion of DBCC CHECKDB, you can increase maxdop (max degree of paralellism) from server configurations before running DBCC CHECKDB. If your system is an oltp system and consists entirely of small transactions, increasing the maxdop may slow the performance of the running system. Therefore, after increasing the maxdop, you should inspect the system and undo the change in any performance problem. To correctly set the maxdop value, you can use the article “sp_configure (Server-Level Configurations in SQL Server)“.
