7

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'.

Improve this question

2 Answers 2

Reset to default
5

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).

Improve this answer
3
  • 2
    In my (admittedly limited) experience with corruption, I've always had to drop and recreate nonclustered indexes, as the rebuild plan uses an index scan (of the corrupt index) because it's cheaper than scanning the base table -- the corruption has always remained every time I've tried this.
    – Jon Seigel
    Commented Mar 12, 2013 at 17:06
  • Yes the issue returned shortly after. I have our sysops people running diagnostics on the (possibly) faulty hardware, tickets open with the IO Vendor, and, just in case, a ticket with Microsoft.
    – Aushin
    Commented Mar 12, 2013 at 18:38
  • 2
    @JonSeigel: yes, you are right. Rebuild will use the same index as source and will keep the corruption, since is a cross key issue. Aushin, try drop and create...
    – Remus Rusanu
    Commented Mar 12, 2013 at 20:19
3

I would back up the db, then drop & recreate those corrupt indexes. Then run the same dbcc periodically to checkup on the problem.

Improve this answer
2
  • This sounds sensible, but I'd prefer it if you weren't user2154687. Are there any risks to the dropping and recreation of corrupt indexes? I would prefer not having to perform a full restore.
    – Aushin
    Commented Mar 12, 2013 at 14:16
  • Since they are non-clustered, in my experiences, they are more for speed then integrity of the table. The only issue with recreating non clustered indexes, is the speed of your applications(s) while recreating them. The size of the tables determine how long it will take to recreate them; also if your application/users are putting locks on the tables(inserts/updates) while your are doing the reindex. Sometimes after hours is the best time.
    – user2154687
    Commented Mar 12, 2013 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.