CREATE UNIQUE INDEX terminated because a duplicate key...
-
In runing a DBCC CHECKDB with a REPAIR I am getting the follwing error that is not being fixed. What is wrong and how might I fix it.
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '13355507'.
Any assistance you could give this newby DBA would be appreciated!
-
Did it give you the table name it errored on?
Run the following query (modify as necessary):
------------------------------
SELECT *
FROM tablename
WHERE indexed_field IN
(SELECT indexed_field
FROM tablename
GROUP BY indexed_field
HAVING COUNT(*) > 1)
-------------------------------
If anything is returned it means that those rows have to be updated so that the indexed field's value is unique.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
No table name. Just
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'XYZ'.
If I get an object number is there a way to get the table name? Is there a way to run CHECKDB to give object names?
-
try
select *
from sysobjects
where id = #
But it is beginning to sound like the DB is corrupted in general. Another way to do this is take the DB offline and then bring it back online. That will force a checkdb. I would suggest a backup before doing anything else.
Have you checked the HW lately? With all the problems you're having, it might be disk problems.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I got the table name with the object id in the error, now I want to get the index field...is the field used to create the index another id somewhere in the record?
Thanks!
-
Go in the EM and select the table, right click on the table and select all tasks -> manage indexes.
That should show you all the indexes and their key fields.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
My problem is that there are many index's there ... how do I know which one is index ID1 ?
If I have no restore point ... my backup is bad ... what are my options?
-
The sysindexes table will tell you which index # 1 is.
Steve
-
Hi again,
When I search for the issue-tables indexes I get back many index's (One for each I see in the GUI) but none have an ID of 1 as indicated in the error message below ... should I be looking in a specific field in sysindexes? Does the Primary key reference in the error indicate that the issues is with that index (PK_Table)?
Original error: duplicate key was found for index ID 1. Most significant primary key is ...
If I have no backup (Hardware issue) is there a way to resolve this issue (then I'll make a backup as the hardware issue is being fixed) without a loss of table data?
Many Thanks
-
Can you access the database at all? It sounds like it.
My suggestion is to stop beating on it. And start fresh.
1) Get a good backup.
2) Create a new database, preferably on the same server.
3) Go to the EM and Right click on the New DB, On the shortcut menu All tasks -> Import Data
4) In the DTS Wizard import all objects.
Keep the old database around. You can generally live without indexes if any fail to import, and recreate them later.
5) sp_Detach OldDB and NewDB. Rename the db files as appropriate and reattach the NewDB with the OldDB name.
6) Check functionality, data integrity, etc.
7) Reattach OldDB as OldDB_reference.
Get good backups and go on with life.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
You know, it sounds like you're dealing with a primary key. Try this, replacing 'table_name' with the name of your table -
select s1.name from sysobjects s1
join sysobjects s2
on s1.parent_obj = s2.id
and s1.xtype = 'pk'
and s2.name = 'table_name'
-- Steve
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply