Sql-server – Two corrupted indexes found by DBCC CheckTable

sql server

I'm wondering if this problem is solvable by dropping/recreating the two indexes. Both are rarely used, nonclustered, non PK, non FK indexes.

Here is the dbcc output:

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

Msg 8951, Level 16, State 1, Line 1

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_ITEMS_ADVFLAG_PARETN_PRICE_STOCK' (ID 70). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1

Data row (1:10834009:15) identified by (Id = 8885719) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 9.30 and NSFPQtyInStock = 0 and Id = 8885719'.

Best Answer

Non-clustered indexes can always be recreated from the base table. No need to drop them just rebuild them:

alter index IX_Advertise_BrandCopy_Price_Stock on BrandCopy rebuild;

Of course, you must figure out how did they end up corrupted. Is Windows reporting IO errors? Does your drive firmware report errors? You need to investigate to figure out what is causing the corruptions and eliminate the problem (likely faulty hardware, disk or memory).

Related Question