How to Use DBCC CHECKTABLE Command in SQL Server Database?
Multiple T-SQL queries are available in SQL for users to eliminate endless errors. In this article, we’re going to have a look at the DBCC CHECKTABLE command in SQL Server. Here, all the details regarding this command like uses, benefits, drawbacks, syntax, etc are mentioned. What this command does is just execute the integrity checks for the pages of a given SQL table. Moreover, it has the capacity to fix errors for a given table as well.
Also, as we have mentioned what to do if DBCC CHECKDB Job failed in SQL, the Plan B for this DBCC CHECKTABLE command is also there in the end. Let’s begin the article with the overview of this command.
Table of Content
DBCC CHECKTABLE SQL Server – Brief Explanation
We all are aware of the CHECKDB command, but here there’s a slight change. This command focuses mainly on tables rather than a complete database. This command can easily fix corruption issues after checking the integrity of pages in SQL table. It can fix damaged tables with deleted records across MDF & NDF files.
Now, let’s have a look at the DBCC CHECKTABLE syntax in detail:
DBCC CHECKTABLE ( 'name_of_table' | 'name_of_view'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
Important Note: Must note that only one table at a time can be handled by this command. Also, it will not work on data objects in the database like stored procedures, triggers, views, rules, etc.
Difference Between DBCC CHECKTABLE and DBCC CHECKDB
We have already understood the basic difference. Another reason for users to opt for the DBCC CHECKTABLE command is to address the entire table errors with utmost accuracy. This is not possible with the other one when we look for an entire database.
However, now, it’s time to find out the major difference that users get in this command. Therefore, we have mentioned that this DBCC CHECKTABLE syntax focuses on a table of the below basis:
- Find if the Index, as well as data pages, are linked correctly to each other or not.
- Check if the rows of a table have an equivalent row in the nonclustered index.
- Check if there is any consistency in the Pointers present.
- The data validation on each page is responsible as well.
- Verifying the sort order of indexes. If it’s correct or not.
It also checks if the partitioned table of index possesses a row & if it’s in the right partition or not. Moreover, it cross-checks the link level consistency among the file system & the table.
Now, to learn how to use DBCC CHECKTABLE cmd, we need to check for corruption first in the table.
How Can A User Find Out the Corruption of A Table?
There are bad pages also known as SUSPECT PAGES in the SQL Server database. The tables of suspect pages are located in the MSDB database. To find out the bad pages here, simply run the below command:
SELECT * From msdb.dbo.suspect pages
There is a problem with this command that users must be aware of. Pages that the database identified as damaged will be detected by this command. This means, all the pages with corruption issues might not be detected by this command.
Therefore, in that case, users need to run the DBCC CHECKTABLE T-SQL CMD.
DBCC CHECKTABLE Command All Consistency Checks
There are several combinations of this command that a user can use to check the table consistency. It can be helpful for users in different scenarios. Below are all six variations of this command mentioned for different use cases.
- Data Page Integrity Check – The Basic One
This is the most basic one & simply checks the data integrity of an individual table in a database.
USE Database1
GO
DBCC CHECKTABLE ('Table1')
- Logical Consistency Check – On Indexes
If users have not mentioned the NOINDEX clause, this command can easily check both the logical as well as physical consistency of a single table along with the nonclustered index.
USE Database1
GO
DBCC CHECKTABLE ('Table1') WITH EXTENDED_LOGICAL_CHECKS,NO_INFOMSGS, ALL_ERRORMSGS;
Users must note that for indexed view, XML index, and spatial indexes, this command only performs the physical checks.
- Time for the Physical Consistency Check
In case, users run this DBCC CHECKDB command with the PHYSICAL_ONLY option, any logical checks will not take place. This is beneficial to cut down the run time and resource usage of the command. It mainly helps users find the torn pages, checksum failures, and common hardware failures present.
USE Database1
GO
DBCC CHECKTABLE ('Table1') WITH PHYSICAL_ONLY;
Tip: Make sure that with the physical-only option, users can not run the repair command. It just helps users to overcome the drawbacks of the logical check command.
- Integrity Consistency Check With NOINDEX Clause
This command does not perform in-depth checks for the non-clustered indexes.
USE Database1
GO
DBCC CHECKTABLE ('Table1',NOINDEX)
- Data Integrity Consistency Check With Lock Option
This command can help users apply a shared table lock on the SQL database individual table rather than utilizing the internal database snapshot:
USE Database1
GO
DBCC CHECKTABLE ('Table1') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS;
- Consistency Check for a Particular Index
In case users want to check a particular index on the basis of their suspect, they can use this command:
USE Database1
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);
How to Use DBCC CHECKTABLE SQL Server to Solve Errors
Just like the CHCEKDB option, here, users get all three options of Repair_Rebuild, Repair_Fast, & Repair_Allow_Data_Loss. Users can select any of these to execute SQL Server repair table task. We are going with the Repair_Rebuild option here.
Run the below command:
USE Database1
GO
ALTER DATABASE Database1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE ('Table1,REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE Database1 SET MULTI_USER;
Now, for the Repair_Allow_Data_Loss & Repair_Fast options, users need to run the same command with just one change. Just replace the repair option with the preferred one. However, we all are aware of the fact that these commands do not always work properly. Therefore, we have a backup plan as well.
What If DBCC CHECKTABLE Command Fails?
In case, users fail to get the desired results after running this command, they can simply trust the automated solution from SysTools known as the SQL Recovery Tool which is the best alternative to DBCC CHECKTABLE syntax. This tool can easily fix an individual table, multiple tables, & even an entire database with ease. Download the Tool now, to get the best results.
The Final Say
After analyzing all of the crucial aspects here, users can easily understand the importance of this DBCC CHECKTABLE in SQL Server. We have explained in detail how to use this CHECKTABLE command syntax to get the maximum benefits. Moreover, users can even try the automated solution if they find it as a better alternative to the manual command.