Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Mar 12 '13 at 16:32

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
    
Thank you. Looks like I have some work ahead of me. –  Aushin Mar 12 '13 at 14:37
    
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 Mar 12 '13 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 Mar 12 '13 at 18:38
    
@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 Mar 12 '13 at 20:19

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

share|improve this answer
    
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 Mar 12 '13 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 Mar 12 '13 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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