Examples
A. Checking a specific table
The following example checks the data page integrity of the HumanResources.Employee table in the AdventureWorks2012 database.SQLCopy
DBCC CHECKTABLE ('HumanResources.Employee');
GO B. Performing a low-overhead check of the table
The following example performs a low overhead check of the Employee table in the AdventureWorks2012 database.SQLCopy
DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;
GO C. Checking a specific index
The following example checks a specific index, obtained by accessing sys.indexes.SQLCopy
DECLARE @indid int;
SET @indid = (SELECT index_id
FROM sys.indexes
WHERE object_id = OBJECT_ID('Production.Product')
AND name = 'AK_Product_Name');
DBCC CHECKTABLE ('Production.Product',@indid); Sample
SELECT TOP 1000 *
FROM dbo.tbTable01
WHERE biID <= 2000
-- An inconsistency was detected during an internal operation. Please contact technical support.
DBCC CHECKTABLE ('dbo.tbTable01');
GO
-- Errors found after running CHECKTABLE
-- To Fix, place database under single user mode and run REPAIR_REBUILD
ALTER DATABASE MyDB01
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKTABLE ('dbo.tbTable01',REPAIR_REBUILD);
GO
DBCC CHECKTABLE ('dbo.tbTable01');
GO
ALTER DATABASE MyDB01
SET MULTI_USER;
GOErrors:
-- DBCC CHECKTABLE ('dbo.tbTable01');
Msg 2511, Level 16, State 1, Line 15
Table error: Object ID 143339575, index ID 1, partition ID 72057599195414528, alloc unit ID 72057599200198656 (type In-row data). Keys out of order on page (3:3451797), slots 108 and 109.
Msg 8935, Level 16, State 1, Line 15
Table error: Object ID 143339575, index ID 1, partition ID 72057599195414528, alloc unit ID 72057599200198656 (type In-row data). The previous link (3:346090) on page (3:297978) does not match the previous page (3:2883736) that the parent (3:3368549), slot 45 expects for this page.
Msg 8934, Level 16, State 2, Line 15
Table error: Object ID 143339575, index ID 1, partition ID 72057599195414528, alloc unit ID 72057599200198656 (type In-row data). The high key value on page (3:3612856) (level 0) is not less than the low key value in the parent (3:3451797), slot 109 of the next page (3:346090).
CHECKTABLE found 0 allocation errors and 52 consistency errors in table 'tbTable01' (object ID 143339575).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDB01.dbo.tbTable01).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- DBCC CHECKTABLE ('dbo.tbTable01',REPAIR_REBUILD);
DBCC results for 'tbTable01'.
Repair: The Clustered index successfully rebuilt for the object "dbo.tbTable01" in database "MyDB01".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tbTable01, idx_tbtbTable01_1" in database "MyDB01".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.tbTable01, idx_tbTable01_2" in database "MyDB01".
Msg 8945, Level 16, State 1, Line 24
Table error: Object ID 143339575, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8935, Level 16, State 1, Line 24
Table error: Object ID 143339575, index ID 1, partition ID 72057599195414528, alloc unit ID 72057599200198656 (type In-row data). The previous link (3:346090) on page (3:297978) does not match the previous page (3:2883736) that the parent (3:3368549), slot 45 expects for this page.
The error has been repaired.
There are 18123222 rows in 154283 pages for object "tbTable01".
CHECKTABLE found 0 allocation errors and 50 consistency errors in table 'tbTable01' (object ID 143339575).
CHECKTABLE fixed 0 allocation errors and 50 consistency errors in table 'tbTable01' (object ID 143339575).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.Message
Executed as user: NT SERVICE\SQLSERVERAGENT. [SQLSTATE 01003] (Message 8153) Unable to find index entry in index ID 2, of table 962102468, in database 'xxxxx'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support. [SQLSTATE HY000] (Error 8646) Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0) Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed.
Comments