DBCC CHECKDB cannot fix database

[CarrahaG]

Hello

I have a problem with a MS SQL Server 2005 SQL database.

If I run the command

DBCC CHECKDB(BHT, REPAIR_REBUILD)


I get the following result:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'pkPDCPS_DOC_LIN'. The duplicate key value is (101321731360, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'cttPDCPS_DOC_LIN'. The duplicate key value is (Aug 10 2011 6:23PM, 101321739271, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'ctwsPDCPS_DOC_LIN'. The duplicate key value is (1, 101321731360, 1).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'pduser.PDCPS_DOC_LIN' and the index name 'cttsPDCPS_DOC_LIN'. The duplicate key value is (1007035, 101321739271, 1).
DBCC results for 'BHT'.


If run the command with the REPAIR_ALLOW_DATA_LOSS, the same error occurs:
DBCC CHECKDB(BHT, REPAIR_ALLOW_DATA_LOSS)



Running the command

select *
from pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)


gives the result:


CDOC_ID CLIN_SEQ_NO PDCCTime PDCDel PDCRepl PDCTSID PDCWSID PDC1 PDC2 PDC1F1 PDC2F1
------------ ----------- -------------------- ------ ------- ------- ------- -------------------- ------- -------------------- -------
101321732044 1 1/21/2012 5:30:44 PM D R 1018571 1 2/3/2012 10:08:04 PM 1000001 2/3/2012 10:08:04 PM 1000001
101321732866 1 1/21/2012 5:31:55 PM D R 1018572 1 2/3/2012 10:08:04 PM 1000001 2/3/2012 10:08:04 PM 1000001
101321732866 2 1/21/2012 5:31:55 PM D R 1018572 1 2/3/2012 10:08:04 PM 1000001 2/3/2012 10:08:04 PM 1000001
101321732866 3 1/21/2012 5:31:55 PM D R 1018572 1 2/3/2012 10:08:04 PM 1000001 2/3/2012 10:08:04 PM 1000001
101321734503 1 1/21/2012 5:34:16 PM D R 1018573 1 2/3/2012 10:08:04 PM 1000001 2/3/2012 10:08:04 PM 1000001
101321735658 1 1/21/2012 5:35:56 PM D R 1018574 1 2/3/2012 10:08:05 PM 1000001 2/3/2012 10:08:05 PM 1000001

6 records selected.




However, if I try to remove the records with the command:

delete pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)



I get the following error:

Error in statement # 1

Internal Query Processor Error: The query processor encountered an
unexpected error during execution

[delete pduser.PDCPS_DOC_LIN
where CDOC_ID in (101321732044, 101321732866, 101321734503, 101321735658)]


The primary key is a clusterred primary key.


My question is how can I remove the records in question? Restoring from a backup is not a good option because there has been data added to the table (and to other tables on the database) after the problem began.


Is there a way to drop the primary key, locate and remove the records in question and then put back the primary key?



Regards,
Georges

 

[gmmastros]

Perhaps you could try copying the data in that table to another one. Then drop the table and rename the copied table to the correct name.

Before you do this, you should make a backup of the database and also make sure that you script everything about the table, including indexes, triggers, etc...



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom