Duplicate Record

Question

Post reply

Duplicate Record

sudhakara

SSCrazy Eights

Points: 9425
More actions
June 4, 2010 at 9:03 pm

#140766

Hi,
While running the rebuild index on the database it has got failed with below error message.
Rebuilding indexes for table 'Mytable' [Microsoft SQL-DMO (ODBC SQLState: 23000)] Error 1505: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Here my question how is it possible to insert or update duplicate key on the key column of unique clustered index.
SQL server 2000 with SP4.
Please help me to simulate and resolve this issue.
Thanks in Advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

Try to recreate index with option IGNORE_DUP_KEY = ON (CREATE INDEX ... WITH DROP_EXISTING = ON, IGNORE_DUP_KEY = ON )

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

or... fix the data.

1st figure it out which PKs are duplicate...

select * from(

select my_primary_key_column, count(*) as headcount

from my_table

group by my_primary_key_column

)

where headcount > 1

2nd take a look at duplicate rows...

select *

from my_table

where my_primary_key_column = whatever_you_got_in_previous_query

3rd talk to the business and fix it - most probably by deleting unwanted rows

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 3

magasvs (6/5/2010)
Try to recreate index with option IGNORE_DUP_KEY = ON (CREATE INDEX ... WITH DROP_EXISTING = ON, IGNORE_DUP_KEY = ON )

Thanks for yours reply.

How is it possible to update or insert the duplicated on the table columns which has the unique clustered index ?

LutzM SSC Guru Points: 107049 More actions · Answer 4

Would you please clarify what you're trying to do?

Will you either end up without duplicates or do you want to insert duplicate values?

If the former, follow Pablos advice. If the latter, a unique index will be the wrong choice.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

I interpreted the situation to be that there are already duplicate records, and the OP was wondering how that's possible since the index being rebuilt disallowed duplicates. So, duplicates should not have been created in the first place.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

It is possible that someone disabled the unique constraint, added data, and then reenabled the constraint with WITH NOCHECK activated (IIRC).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 7

lmu92 (6/6/2010)
Would you please clarify what you're trying to do?
Will you either end up without duplicates or do you want to insert duplicate values?
If the former, follow Pablos advice. If the latter, a unique index will be the wrong choice.

Maintanance paln trying to rebuild the index.but it is not successed since the duplicate keys are there.i am not trying to do any dml operations on the table.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 8

Jeff Moden (6/6/2010)
It is possible that someone disabled the unique constraint, added data, and then reenabled the constraint with WITH NOCHECK activated (IIRC).

As per your post i have tried the but its failing.Please check the following code which i used to verfity

--created table

create table duplicaterecord(a int not null primary key)

--Insert Record

insert duplicaterecord

select 1

--Disable the constratins

ALTER TABLE duplicaterecord NOCHECK CONSTRAINT PK__duplicaterecord__0AD2A005

--Inserting duplicate record

insert duplicaterecord

select 1

--Getting error message

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__duplicaterecord__0AD2A005'. Cannot insert duplicate key in object 'dbo.duplicaterecord'.

The statement has been terminated.

Please check and post your comments.

Thanks in Advances.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 9

sudhakara

SSCrazy Eights

Points: 9425

June 8, 2010 at 2:07 am

#1177343

Hi,

Any update on this ?

Thanks in Advance.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Yes... I've not been able to force a dupe on a real PK constraint so that's probably not it.

Have you determined that there are actually dupes? If there are, then you'll need to remove those dupes somehow. Either that, or (I believe) the table may have some form of corruption. Gail would know these symptoms much better than I, though. Let's hope she stops by.

Also, What is the current "Transaction Isolation Level" for the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tpaulsen SSC Enthusiast Points: 195 More actions · Answer 11

I would try running DBCC CHECKTABLE ("MyTable");

If that returns an error with the current unique clustered index, I would restore the database from a backup. If that is not an option, try running

DBCC CHECKTABLE ("MyTable", REPAIR_REBUILD);

If there are constraints on the table, you should run DBCC CHECKCONSTRAINTS if you had to do a repair.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 12

tpaulsen (6/9/2010)
I would try running DBCC CHECKTABLE ("MyTable");
If that returns an error with the current unique clustered index, I would restore the database from a backup. If that is not an option, try running
DBCC CHECKTABLE ("MyTable", REPAIR_REBUILD);
If there are constraints on the table, you should run DBCC CHECKCONSTRAINTS if you had to do a repair.

Hi,

Server giving the below eror message while running the dbcc checktable(mytable).

Server: Msg 2511, Level 16, State 2, Line 1

Table error: Object ID 2105058535, Index ID 0. Keys out of order on page (1:2528), slots 0 and 1.

Server: Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 2105058535, Index ID 4. Keys out of order on page (1:142427), slots 32 and 33.

Server: Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 2105058535, Index ID 4. Keys out of order on page (1:142427), slots 34 and 35.

DBCC results for 'MyTable'.

There are 12123 rows in 740 pages for object 'MyTable'.

CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'MX_Properties' (object ID 2105058535).

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (SERVer.dbo.mytable ).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

ALTER DATABASE <db name> SET SINGLE_USER

DBCC CHECKTABLE ('MyTable', REPAIR_REBUILD);

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass