DBCC CHECKTABLE Command On SQL Server

dbtut

6 years ago

DBCC CHECKTABLE is used to test the consistency of a table or indexed view. The DBCC CHECKTABLE command performs the same operations as the DBCC CHECKDB command for a table in the database.

You may want to read my article “DBCC CHECKDB Command On SQL Server“.

DBCC CHECKTABLE has several different uses. Let’s examine them all one by one:

Consistency Check:

The following command detects logical and physical errors in the TestTable table in the Test database.

USE Test
GO
DBCC CHECKTABLE ('TestTable')

USE Test

DBCC CHECKTABLE ('TestTable')

Consistency Check With NOINDEX:

The following query detects errors other than non-clustered indexes in the TestTable table.

USE Test
GO
DBCC CHECKTABLE (N'TestTable',NOINDEX)

USE Test

DBCC CHECKTABLE (N'TestTable',NOINDEX)

Correct Errors With REPAIR_REBUILD:

You can run the DBCC CHECKTABLE command to correct errors in the non-clustereded indexes in the table by using the following command without any loss of data.

The REPAIR_REBUILD operation does not fix any errors that contain filestream data.

USE Test
GO
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE (N'TestTable',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE Test SET MULTI_USER;

USE Test

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKTABLE (N'TestTable',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;

ALTER DATABASE Test SET MULTI_USER;

Correct Errors With REPAIR_ALLOW_DATA_LOSS:

You can run the DBCC CHECKTABLE command to correct all errors in the table with the risk of data loss by using the following command.

USE Test
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE (N'TestTable', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE [Test] SET MULTI_USER;

USE Test

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKTABLE (N'TestTable', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

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 CHECKTABLE 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 CHECKTABLE 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“.

USE Test
GO
DBCC CHECKTABLE (N'TestTable') WITH EXTENDED_LOGICAL_CHECKS,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKTABLE (N'TestTable') 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 shared table lock on the table instead of using the internal database snapshot. You may want to read my article “SQL Server Lock Types“.

USE Test
GO
DBCC CHECKTABLE (N'TestTable') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKTABLE (N'TestTable') 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.

USE Test
GO
DBCC CHECKTABLE (N'TestTable') WITH ESTIMATEONLY,NO_INFOMSGS, ALL_ERRORMSGS;
Estimated TEMPDB space (in KB) needed for CHECKTABLE on database Test = 1.

USE Test

DBCC CHECKTABLE (N'TestTable') WITH ESTIMATEONLY,NO_INFOMSGS, ALL_ERRORMSGS;

Estimated TEMPDB space (in KB) needed for CHECKTABLE on database Test = 1.

Only PHYSICAL Consistency Check:

When you run it using the command below, only physical consistency check is performed. It is not done logically.

If you ask me, it doesn’t make sense to run it using this command. Because it doesn’t control its logical consistency. Of course, they offered us such an option to be used in special cases.

With the PHYSICAL_ONLY command, you cannot perform any REPAIR operation.

USE Test
GO
DBCC CHECKTABLE (N'TestTable') WITH PHYSICAL_ONLY,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKTABLE (N'TestTable') 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.

USE Test
GO
DBCC CHECKTABLE (N'TestTable') WITH DATA_PURITY,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKTABLE (N'TestTable') WITH DATA_PURITY,NO_INFOMSGS, ALL_ERRORMSGS;

For faster completion of DBCC CHECKTABLE, you can increase maxdop (max degree of paralellism) from server configurations before running DBCC CHECKTABLE. 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)“.

DBCC CHECKTABLE For an Index:

If you want, you can run the DBCC CHECKTABLE command for just one index. In the script below, we test the clustered index called PK_TestTable in the TestTable table.

USE Test
GO
DECLARE @IndexId int;   
SET @IndexId = (SELECT index_id    
              FROM sys.indexes   
              WHERE object_id = OBJECT_ID('TestTable')   
                    AND name = 'PK_TestTable');   

DBCC CHECKTABLE ('TestTable',@IndexId);

USE Test

DECLARE @IndexId int;

SET @IndexId = (SELECT index_id

FROM sys.indexes

WHERE object_id = OBJECT_ID('TestTable')

AND name = 'PK_TestTable');

DBCC CHECKTABLE ('TestTable',@IndexId);