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 am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here.

When I run a select query for these key values, it returns only one row, but when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) these errors are thrown:

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'uc1SycContents'. The duplicate key value is (7696031, 08703987-557d-e111-9888-e61f13c44f03).
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'nc2MSmerge_contents'. The duplicate key value is (424222, 7696031, 08703987-557d-e111-9888-e61f13c44f03).
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'nc4MSmerge_contents'. The duplicate key value is (08703987-557d-e111-9888-e61f13c44f03, 7696031).
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'nc3MSmerge_contents'. The duplicate key value is (-425819, 7696031, bf8a5a9c-6f7d-e111-9888-e61f13c44f03).
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory' and the index name 'unc1MSmerge_genhistory'. The duplicate key value is (236bfa48-965e-4a65-b4cd-ac527de04d1b, ).

How can I repair my database?

share|improve this question

migrated from stackoverflow.com Apr 5 '12 at 11:56

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

1  
I may be wrong, but I believe you'll have to remove or change the records that contain the duplicated key value, otherwise I don't think it will be able to create the new index. Step 1: Backup the DB. –  Cylindric Apr 5 '12 at 10:21
    
Not a programming question. Suggest this is moved to either DBA or ServerFault. –  Cylindric Apr 5 '12 at 10:22
    
i am not able to find duplicate records :( –  Aamod Thakur Apr 5 '12 at 10:48
    
So none of those values that it lists in the error? –  Cylindric Apr 5 '12 at 12:12
    
Why did you run DBCC with "REPAIR_ALLOW_DATA_LOSS"? Restore from backup: if you have no backup, your data isn't important. –  gbn Apr 5 '12 at 12:17
show 3 more comments

1 Answer 1

up vote 1 down vote accepted

if you consider error, Msg 1505, Level 16, State 1, Line 2 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'uc1SycContents'. The duplicate key value is (7696031, 08703987-557d-e111-9888-e61f13c44f03)...... I am running below Query
select * from msmerge_contents where rowguid='08703987-557d-e111-9888-e61f13c44f03'
and it is returning only 1 row

When you have index corruption problems (ie. keys present in NC index but not in base table or vice-versa) you must be very careful about the SQL you use to validate data. At this moment your data is inconsistent but the query optimizer does not know that and completely trusts your schema, including these incorrect indexes. As such it may optimize your query to use one of the NC indexes that is missing a key and the result will also miss a a key falsely returning no duplicates. To solve this catch-22 situation you need to force the optimizer hand by explicitly requesting an index or another and make sure the projected list of columns can be satisfied by the index you enforced (ie. no *). Assuming uc1SycContents is not the clustered index, try out the following:

select rowguid
from msmerge_contents with INDEX (1)
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

select rowguid
from msmerge_contents with INDEX ([uc1SycContents])
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

This will forcefully check if the rowguid has a duplicate for that guid in the base table clustered index (index id 1) vs. the index uc1SycContents. I expect that the first query returns 2 (or more) rows while the second returns 1.

share|improve this answer
    
i ran the queries you provided but it returned 1 row for both the queries. (i had to put paranthesis before and after index (INDEX (1)) –  Aamod Thakur Apr 5 '12 at 17:53
    
Is uc1SycContents the clustered index? –  Remus Rusanu Apr 5 '12 at 18:07
    
Yes it is the clustered index –  Aamod Thakur Apr 5 '12 at 18:14
    
By the way i tried the same for table MSmerge_genhistory, and it gave me two rows after rebuilding indexes. same doesnt work for msmerge_contents –  Aamod Thakur Apr 5 '12 at 18:18
1  
If uc1SysContents is the clustered index then the two queries above are basically the same. The important thing is to understand how you can force a specific index to query the data. Hopefully this will lead you to identifying the duplicates. You can try deactivating all indexes, remove the duplicate values, then enable back the indexes (will trigger a rebuild). –  Remus Rusanu Apr 5 '12 at 18:30
show 2 more comments

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.