June 4, 2010 at 9:03 pm
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.
June 5, 2010 at 9:55 am
Try to recreate index with option IGNORE_DUP_KEY = ON (CREATE INDEX ... WITH DROP_EXISTING = ON, IGNORE_DUP_KEY = ON )
June 5, 2010 at 1:02 pm
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.June 5, 2010 at 7:47 pm
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 ?
June 6, 2010 at 4:57 am
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.
June 6, 2010 at 8:45 am
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.
June 6, 2010 at 4:32 pm
It is possible that someone disabled the unique constraint, added data, and then reenabled the constraint with WITH NOCHECK activated (IIRC).
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2010 at 3:00 am
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.
June 7, 2010 at 3:06 am
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.
June 8, 2010 at 2:07 am
Hi,
Any update on this ?
Thanks in Advance.
June 8, 2010 at 7:40 pm
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
Change is inevitable... Change for the better is not.
June 9, 2010 at 10:44 am
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.
June 9, 2010 at 9:55 pm
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 ).
June 10, 2010 at 8:24 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply