SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 dbcc could not repair the error need help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

greegy08
Starting Member

Philippines
19 Posts

Posted - 03/16/2010 :  21:29:14  Show Profile  Reply with Quote
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
5072 Posts

Posted - 03/16/2010 :  22:46:08  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/17/2010 :  03:41:56  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

greegy08
Starting Member

Philippines
19 Posts

Posted - 03/18/2010 :  20:55:16  Show Profile  Reply with Quote
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
Go to Top of Page

greegy08
Starting Member

Philippines
19 Posts

Posted - 03/18/2010 :  21:02:33  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/19/2010 :  01:00:30  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

greegy08
Starting Member

Philippines
19 Posts

Posted - 03/22/2010 :  01:49:54  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/22/2010 :  13:31:38  Show Profile  Visit russell's Homepage  Reply with Quote
glad to help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000