none
Detected issue after a DBCC CHECKDB WITH ALL_ERRORMSGS

    Question

  • Hi,

    I'm controlling a db of a my customer by using the statement DBCC CHECKDB WITH ALL_ERRORMSG and I've detected some errors about "... a reference from the previous page is missing ... Possible problem in chain connection ...".

    Any suggests to manage a such physical error?

    Thanks

    Tuesday, May 8, 2018 6:00 PM

All replies

  • Please post the complete message which you got in "RED" also at last it suggests some repair, what is that ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, May 8, 2018 6:09 PM
    Moderator
  • The message is in Italian Language: is the same for you?

    Thanks

    Tuesday, May 8, 2018 6:11 PM
  • The message is in Italian Language: is the same for you?

    I guess that Shanky can make sense out of it, even if he has no understanding of Italian, as the message includes which repair mode you need with DBCC and that is spelt out as an option. In English.

    If it says REPAIR_ALLOW_DATA_LOSS, the best way to handle the situation is to restore from a clean backup. This is because the option means what it says, you will lose data. Everything that DBCC cannot piece together it will just drop. And that can be the better part of a 215 GB table.

    If there is no good backup (you should run DBCC after RESTORE as well!), you can try REPAIR_ALLOW_DATA_LOSS, but on a copy of the database, so that you can assess the impact.

    Also beware that databases do not become corrupt out of the blue, but it is a clear sign of bad hardware, and if you stay on that hardware, the problem may come back.

    Tuesday, May 8, 2018 9:11 PM
  • The message is in Italian Language: is the same for you?

    Thanks

    Adding to what Erland said, I can tale help of Google Translate :). Please post the message

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 9, 2018 6:31 AM
    Moderator
  • Hi,

    thanks for your replies.

    My customer periodically executed a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS on 4 databases in order to allow the functioning of a custom solution.

    I'm controlling these databases restored on a test machine. The related backups were made after a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS operation.

    Controlling one of these 4 databases with ALL_ERRORMSG option I've seen these messages (below translated in English language):

    Table error: object ID 1957582012, index ID 1, partition ID 72057594047102976, allocation unit ID 72057594051756032 (type In-row data). On page (1: 871869) a reference from the previous page is missing (1: 871867). Possible problem in chain connection.

    CHECKDB found 1 allocation errors and 1 consistency errors in the 'xxx' database.repair_allow_data_loss is the minimum correction level for errors detected by DBCC CHECKDB (xxx).DBCC execution completed. If you have displayed DBCC error messages, contact your system administrator

    Any suggests to me, please? Is the database recoverable? Thanks

    Wednesday, May 9, 2018 7:34 AM
  • Hi,

    thanks for your replies.

    My customer periodically executed a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS on 4 databases in order to allow the functioning of a custom solution.

    I'm controlling these databases restored on a test machine. The related backups were made after a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS operation.

    Controlling one of these 4 databases with ALL_ERRORMSG option I've seen these messages (below translated in English language):

    Table error: object ID 1957582012, index ID 1, partition ID 72057594047102976, allocation unit ID 72057594051756032 (type In-row data). On page (1: 871869) a reference from the previous page is missing (1: 871867). Possible problem in chain connection.

    CHECKDB found 1 allocation errors and 1 consistency errors in the 'xxx' database.repair_allow_data_loss is the minimum correction level for errors detected by DBCC CHECKDB (xxx).DBCC execution completed. If you have displayed DBCC error messages, contact your system administrator

    Any suggests to me, please? Is the database recoverable? Thanks

    Corruption seems to be on Index ID 1 which is clustered index so dropping and rebuilding index in not going to fix things. If you run repair allow data loss it would delete few things and as you know it will not tell you what it deleted and what constraints it removed. If you have clean backup restore from it.

    Also why your customer "periodically" executed repair_allow_data_loss this is a blunder, does he know what this command does ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 9, 2018 8:00 AM
    Moderator
  • Hi,

    my customer executed periodically the DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS to proof to restart the blocked custom solution; he doesn't know that this isn't a good practice to do.

    I'm working on a separate machine where my customer has restored the backups of the 4 database to control: he executed a first DBCC CHECKDB (with repair_allow_data_loss option) on the databases to backup, then backupped the databases, restored the dbs and executed a second DBCC CHECKDB (with repair_allow_data_loss option) on the restored databases. These are the starting database for my checks.

    Now, which suggested actions could I do in order to restore the functioning of the first database that I'm analyzing, please?

    Thanks

    Wednesday, May 9, 2018 9:29 AM
  • Restore from valid backup, repair allow data loss is not guaranteed to remove all corruption and this seems like happening with your customer as even though he ran repair allow data loss you still see corruption. If you don't have backup you don't have much option

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 9, 2018 9:52 AM
    Moderator
  • Moreover, for the same database I've noticed 1 allocation error.

    Does it exist another sure tool in order to repair data, different from DBCC CHECKDB?

    Thanks

    Wednesday, May 9, 2018 10:07 AM
  • I've accomplished these actions:

    a. to run the DBCC CHECKDB(xxx, REPAIR_ALLOW_DATA_LOSS) command, that has detected duplicate rows for a certain table and a certain value of the related primary key. With the actual primary key I cannot detect any duplicate rows;

    b. to drop manually the primary key;

    c. to detect the duplicate rows;

    d. to delete the duplicate rows;

    e. to recreate the primary key;

    f. to re-run the DBCC CHECKDB(xxx, REPAIR_ALLOW_DATA_LOSS) command, that has correct 1 consistency error and 1 allocation error;

    g. to validate the accomplished actions, to run the DBCC CHECKDB WITH ALL_ERRORMSGS that has strangely detected the allocation error corrected (?)

    Now, is it possible to correct really an allocation error detected by the DBCC CHECKDB statement?

    Moreover, is it possible to solve a such consistency error:

    Catalog control message 3853, status 1: For the attribute (object_id = 1004214857) of the row (object_id = 1004214857, column_id = 1) of sys.columns, there is no corresponding row (object_id = 1004214857) in sys.objects?

    Thanks

    • Edited by pscorca Wednesday, May 9, 2018 1:17 PM update
    Wednesday, May 9, 2018 12:39 PM
  • My customer periodically executed a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS on 4 databases in order to allow the functioning of a custom solution.

    Periodically?

    For crying out load, get new hardware!

    As for whether you can recover that database, I would suggest that the best is to create an empty database from scripts, and then use INSERT SELECT, BPC, SSIS, the import/export wizard or whatever you fancy to try to get as much data as you can.

    There are tools out there that claim that they can fix corruptions that DBCC cannot fix, but they or they not be able to fix your actual corruption. Each corruption is essentially unique. It may be possible to repair the database if you know the page structure in SQL Server and you also have understanding of the data, but it is very time-consuming. And definitely not trivial.

    Again, if they need to run DBCC repeatedly, their top priority is to move the database to a new machine NOW!

    Wednesday, May 9, 2018 9:25 PM
  • Ok, I'm trying to move a database having an huge number of tables on a test machine. So, I'm trying to generate the related script for the only schema, obtaining an error for a table signaled as unexisting but rightly present in the database.

    Does it exist another manner to generate the script schema more sure?

    Thanks

    Wednesday, May 16, 2018 8:49 AM
  • Does it exist another manner to generate the script schema more sure?

    Does SELECT * INTO othertbl FROM tbl WHERE 1 = 0 work? If so, you can use the schema of othertbl to generate the schema.

    Else you will have to ask around if anyone knows the schema of the table. Who knows, maybe the source code is under version control? Or there is some very old backup from which you can get the schema.

    Of course, all this presumes that you can select data from the table at all...

    Wednesday, May 16, 2018 7:23 PM
  • Hi Erland,

    using SELECT * INTO statement to generate the schema of tens of thousands of tables?

    It seems impossible to do.

    Moreover, SELECT * INTO statement loses the primary Keys.

    Does it exist any limitations to use the script generator by SSMS?

    Thanks


    • Edited by pscorca Thursday, May 17, 2018 8:00 AM updating
    Wednesday, May 16, 2018 9:24 PM
  • SELECT INTO takes one table of course. You would use that for the tables you can't script because there is problem with scripting due to the database corruption.

    Yes, SELECT INTO loses the primary keys. Hopefully there is someone on site that can help you to figure out what the keys might be.

    I like to point out that what you are into is not a walk in a park. This is a high-risk operation with limited chance for success.

    Thursday, May 17, 2018 9:07 PM