dbcc could not repair the error need help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

dbcc could not repair the error need help!

Author

Topic

greegy08
Starting Member

19 Posts

Posted - 2010-03-16 : 21:29:14

good day
this is the result of dbcc w/ repair_allow_data_loss

Server: Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_cicntp'. Cannot insert duplicate key in object 'cicntp'.
Server: Msg 8951, Level 16, State 1, Line 9
Table error: Table 'cicntp' (ID 1417772108). Missing or invalid key in index 'PK_cicntp' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 9
Data row (1:417979:2) identified by (RID = (1:417979:2) cnt_id = 334F6E57-486A-4308-B855-837F32893F38ÀžÛ) has index values (ID = 1889 and cnt_id = 334F6E57-486A-4308-B855-837F32893F38H§Û).
DBCC results for 'cicntp'.
Could not repair this error.
There are 6065 rows in 550 pages for object 'cicntp'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'cicntp' (object ID 1417772108).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (363.dbo.cicntp repair_allow_data_loss).

greegy08
SQL NooB

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-16 : 22:46:08

try dropping and recreating pk on cicntp. if this pk is clustered, you likely have real data loss at this point.

if you have a good backup, you can restore it to a different db and then copy the table over from there

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-17 : 03:41:56

Agreed, drop and recreate of the primary key should fix this. Don't rebuild it, drop it and then recreate it. There's no data loss, the pk is nonclustered (id 2). I'd hazard a guess that there is no clustered index on this table.

One question...
The checkDB output said that repair_fast was the level necessary to repair this. So why did you run repair_allow_data_loss?

--
Gail Shaw
SQL Server MVP

greegy08
Starting Member

19 Posts

Posted - 2010-03-18 : 20:55:16

quote:
Originally posted by russell

try dropping and recreating pk on cicntp. if this pk is clustered, you likely have real data loss at this point.

if you have a good backup, you can restore it to a different db and then copy the table over from there

hi russel,

thanks for the response,
I try already try dropping and recreating but still error occurs.

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '1889'.
The statement has been terminated.
Server: Msg 3703, Level 11, State 7, Line 2
Cannot drop the index 'cicntp.icntx4', because it does not exist in the system catalog.
Server: Msg 1913, Level 16, State 1, Line 8
There is already an index on table 'cicntp' named 'cicntp'.
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 4. Most significant primary key is '-- '.
The statement has been terminated.

And also I can not export all the data in table cicntp to another db.

Error "Violation of PRIMARY KEY constraint 'PK_cicntp'. Cannot insert duplicate key in object 'cicntp'."

greegy08
sqlnoob

greegy08
Starting Member

19 Posts

Posted - 2010-03-18 : 21:02:33

quote:
Originally posted by GilaMonster

Agreed, drop and recreate of the primary key should fix this. Don't rebuild it, drop it and then recreate it. There's no data loss, the pk is nonclustered (id 2). I'd hazard a guess that there is no clustered index on this table.

One question...
The checkDB output said that repair_fast was the level necessary to repair this. So why did you run repair_allow_data_loss?

--
Gail Shaw
SQL Server MVP

hi gail,

i try to run repair_rebuild, repair_fast, and even repair_allow_data_loss cannot fix this error.

Please help me how to fix this error.
thanks for your reply.

greegy08
SQLnoob

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-19 : 01:00:30

you're going to have to find the duplicate. you can do it like this...let's assume your PK is a single column named "id"


SELECT	id, count(id) c
FROM	cicntp
GROUP BY
	id
HAVING	COUNT(id) > 1

This will show you the id that has dupes. You'll have to decide which are invalid, or if none are, manually modify the value(s).

Then you can re-apply the PK

greegy08
Starting Member

19 Posts

Posted - 2010-03-22 : 01:49:54

Hi Russell,

Thanks for the info. Now i know the duplicate data of the table. I have to trace and decide which is invalid to this.

Thanks a lot for your time.

greegy08
SQLnoob

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-22 : 13:31:38

glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources