All About DBCC CHECKTABLE – When and How to Use It

Free download Stellar Repair for MS SQL

DBCC CHECKTABLE is a database console command (dbcc) statement that can perform integrity checks on table structure and pages. Before discussing how to use the DBCC CHECKTABLE command, let’s have a brief overview of it.

A Brief Overview of DBCC CHECKTABLE

As a SQL user or database administrator, you must have used the DBCC CHECKDB command to check for database integrity and repair corrupted databases.

Quick Solution: While DBCC CHECKTABLE can help repair specific tables, it cannot recover deleted data. Use SQL Repair Software to avoid the risk of losing deleted table records from the SQL database. The software also helps recover all the objects from SQL database MDF and NDF files, maintaining data integrity. Try the demo version of the software to ascertain its functionality.

DBCC CHECKTABLE has almost the same syntax and performs the same operations as DBCC CHECKDB.

DBCC Checktable Syntax

DBCC CHECKTABLE ( 'table_name' | 'view_name'
        [ , NOINDEX
            | index_id
            | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
        ]
    ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ]
                    [ , [ PHYSICAL_ONLY ] ]
                }
        ]

The CheckTable command checks the integrity of one table at a time. The DBCC CHECKDB command, on the other hand, helps check the integrity of all the objects in a database.

DBCC CHECKDB vs DBCC CHECKTABLE

Note: You need to run DBCC Checkdb to perform DBCC CHECKTABLE on all tables in a database.

Further, the CheckTable command, unlike DBCC CHECKDB, follows a more drilled-down approach to test the specified table for the following:

  • Index and data pages are linked correctly.
  • The sort order of indexes is correct.
  • There is consistency in Pointers.
  • Validate the data on each page is reasonable.
  • Every row in the db table has a similar row in each non-clustered index.
  • A partitioned table or index contains a row that is in the correct partition.
  • There should be link-level consistency between the file system and table when storing varbinary(max) data in the file system.

Before proceeding further, let's find out if the database table is corrupted. If you’re lucky enough, you may receive an error indicating SQL Server table corruption. If not, check the next section to know how to check for table corruption.

How to Check for SQL Table Corruption?

You can check for corruption in a SQL table by checking for bad pages in suspect_pages table. The suspect_pages table is stored in the msdb db.

Execute the following query to look for bad pages:

SELECT * From msdb.dbo.suspect pages

Note: The above query might not always result in correct results as it includes pages that are marked corrupted, and only the corrupted pages that have been accessed are classified as marked. And so, you must include the DBCC CHECKDB command to detect the undetected issues thoroughly. The checkdb command helps find out the logical and physical integrity of all the database objects. If the query returns zero rows, it means that there are no corrupt pages.

Using DBCC CHECKTABLE Command on SQL Server Database Table

There are different uses of DBCC CHECKTABLE. Let’s examine its primary uses one by one.

Using DBCC CHECKTABLE to Perform Consistency Checks

Note: In the following queries, we will be using ‘Table1’ named table in the Testdbdb database. Make sure to replace ‘Table1’ with the name of your table. Also, change the Testdb database with your db name.

1. Check for Data Page Integrity

The following query helps check data page integrity of an individual table:

USE Testdb
GO
DBCC CHECKTABLE ('Table1')

2. Performing Logical Consistency Checks on Indexes

Unless NOINDEX is included, the DBCC CHECKTABLE command helps check both the physical and logical consistency of a single table and its non-clustered indexes. However, the checktable command only performs physical consistency checks on the indexed view, XML index, and spatial indexes. But, using the following command can help you Testdb the logical consistency for indexed view, XML, and spatial indexes:

USE Testdb
GO
DBCC CHECKTABLE ('Table1') WITH EXTENDED_LOGICAL_CHECKS,NO_INFOMSGS, ALL_ERRORMSGS;

3. Performing Physical Consistency Check

Running the DBCC CHECKTABLE command with the ‘PHYSICAL_ONLY’ option checks the table's physical consistency and won’t perform any logical checks. Using this option helps reduce run-time and resource usage of DBCC CHECKTABLE.

Essentially, the PHYSICAL_ONLY option reads and checks the integrity of every page. It also helps detect torn pages, checksum failures, and common hardware failures.

Use the following query when you want to bypass all the logical checks (and limit performing check on page structure):

Note: You cannot run DBCC CHECKTABLE to perform any REPAIR operation when using the PHYSICAL_ONLY command.


USE Testdb
GO
DBCC CHECKTABLE ('Table1') WITH PHYSICAL_ONLY;

4. Consistency Check With NOINDEX:


The query below helps detect errors in a database table but skips performing intensive checks of non-clustered indexes:

USE Testdb
GO
DBCC CHECKTABLE ('Table1',NOINDEX)

5. Consistency Check With Lock

Use the following command to place a shared table lock on the table instead of using the internal database snapshot:

USE Testdb
GO
DBCC CHECKTABLE ('Table1') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS;

6. Checking Consistency of a Specific Index

The following command can be used to run DBCC CHECKTABLE command for a specific index:

USE Testdb
GO
DECLARE @IndexId int;
SET @IndexId = (SELECT index_id
              FROM sys.indexes
              WHERE object_id = OBJECT_ID('Table1')
                    AND name = 'Index_Table1');
DBCC CHECKTABLE ('Table1',@IndexId);

Using DBCC CHECKTABLE to Fix Table Errors

If DBCC CHECKTABLE reports any consistency errors in a table, you must first try to restore the data from a known good backup. However, if the backup is not up to date or corrupted, you will need to run DBCC CHECKTABLE with Repair.

Read this: How to Recover SQL Server Database from a Corrupt Backup File?

Note: If you must use Repair, run the DBCC CHECKTABLE command without a repair option to find which repair level you should use. If you choose to use the Repair_Allow_Data_Loss option, make sure to back up your db first.

Fix Table Errors using Repair Options

  • Repair Table using DBCC CHECKTABLE with REPAIR_REBUILD

You can use the DBCC CHECKTABLE command to fix errors in the non-clustered indexes in a SQL table by using the below command:

Note: The REPAIR_REBUILD operation does not correct any errors containing filestream data.

USE Testdb
GO
ALTER DATABASE Testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE ('Table1,REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE Testdb SET MULTI_USER;
  • Repair Table using DBCC CHECKTABLE with REPAIR_ALLOW_DATA_LOSS

You can run the DBCC CHECKTABLE command to fix corruption in the table by using the following command.

Note: The REPAIR_ALLOW_DATA_LOSS option, as the name implies, can lead to data loss.

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

Note: After running DBCC CHECKTABLE with the repair options ‘REPAIR_REBUILD’ or ‘REPAIR_ALLOW_DATA_LOSS’, it is recommended that you must run the DBCC CHECKCONSTRAINTS command. This is because the repair options do not consider any constraints on or between SQL database tables.

End Note

If you cannot restore corrupted table data by using the DBCC CHECKTABLE command, or need to recover deleted table records, using a specialized SQL Recovery tool such as Stellar Repair for MS SQL may help. The software helps retrieve all the SQL database components, including table, deleted table records, indexes, views, etc. in a few simple steps. It can be used to recover table data on both Windows and Linux systems, preserving the original table structure and formatting.

 



Was this article helpful?
About The Author
author image
Charanjeet Kaur linkdin Icon

Technical writer with over 7 years of experience

Table of Contents

WHY STELLAR® IS GLOBAL LEADER

Why Choose Stellar?
  • 0M+

    Customers

  • 0+

    Years of Excellence

  • 0+

    R&D Engineers

  • 0+

    Countries

  • 0+

    PARTNERS

  • 0+

    Awards Received