| Author |
 Topic  |
|
|
greegy08
Starting Member
Philippines
19 Posts |
 Posted - 03/16/2010 : 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
USA
4967 Posts |
Posted - 03/16/2010 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 03/17/2010 : 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
Philippines
19 Posts |
Posted - 03/18/2010 : 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
Philippines
19 Posts |
Posted - 03/18/2010 : 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
USA
4967 Posts |
Posted - 03/19/2010 : 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
Philippines
19 Posts |
Posted - 03/22/2010 : 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
USA
4967 Posts |
Posted - 03/22/2010 : 13:31:38
|
| glad to help |
|
|
| |
Topic |
|
dbcc could not repair the error need help!
