SQL Server Performance

DBCC CheckDB - consistency errors

Discussion in 'General DBA Questions' started by newchick, Aug 29, 2005.

  1. newchick New Member

    Hi,
    Please help. I am new with SQL server stuff and I am not a DBA (My company doesn't have DBA currently).
    I ran the DBCC CHECKDB for a table named: BillHeaders and I got this error:

    DBCC results for 'BillHeaders'. Msg 8928, Level 16, State 1, Server NIMITZ, Line 1 Object ID 1061578820, index ID 0: Page (1:23071) could not be processed. See other errors for details.
    The repair level on the DBCC statement caused this repair to be bypassed. Msg 8944, Level 16, State 18, Server NIMITZ, Line 1 Table
    error: Object ID 1061578820, index ID 0, page (1:23071), row 10. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 360 and 474.
    The repair level on the DBCC statement caused this repair to be bypassed. Msg 8976, Level 16, State 1, Server NIMITZ, Line 1 Table
    error: Object ID 1061578820, index ID 1. Page (1:23071) was not seen in the scan although its parent (1:23027) and previous (1:23070) refer to it. Check any previous errors.
    The repair level on the DBCC statement caused this repair to be bypassed. Msg 8978, Level 16, State 1, Server NIMITZ, Line 1 Table
    error: Object ID 1061578820, index ID 1. Page (1:23072) is missing a reference from previous page (1:23071). Possible chain linkage problem.
    The repair level on the DBCC statement caused this repair to be bypassed. There are 2637546 rows in 211486 pages for object 'BillHeaders'.
    CHECKDB found 0 allocation errors and 4 consistency errors in table 'BillHeaders' (object ID 1061578820). CHECKDB found 0 allocation errors and 4 consistency errors in database 'Bills'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Bills repair_rebuild).
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. 0 DataBase had errors backing up

    I had ran the DBCC CHECKTABLE ('billheaders', REPAIR_FAST); It didn't fix them. then I ran another one DBCC CHECKTABLE ('billheaders', REPAIR_REBUILD); It didn't work either. Then I ran DBCC DBREINDEX ('billheaders'); it brought the server down (yeah, you wouldn't imaging how panic I was). Do you know if DBCC DBREINDEX would help on this? (I had to stop it b/c it ran too long; do you know how long should it take to run this reindex for a data size of 10gb?)

    It's recomended to run with REPAIR_ALLOW_DATA_LOSS; but I don't think my boss would like to hear about this (data loss); is there any other alternative way(s) to fix the errors without losing data? what caused these errors do you know?

    Any words would be greatly appreciated.

    Lily
  2. sainidaljit New Member

    You can use the following DBCC commands to overcome these errors

    1. Run DBCC CHECKDB with REPAIR clause
    2. Run DBCC CHECKALLOC to fix any allocation problems.

    Thanks


    Thanks N' Regards
    DJ
  3. newchick New Member

    Thanks DJ.

    I did run the CHECKDB with REPAIR before I ran the CHECKTABLE. Neither of them fix errorS. The DBCC CHECKALLOC fix allocation error which I do not have. I just have the consistency errors. Are they the same?

    Lily
  4. Luis Martin Moderator

    No, if you still have consistency errors before REPAIR CLAUSE, you have to evaluate the option With REPAIR_ALLOW_DATA_LOSS.

    Some times is the only way.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. FrankKalis Moderator

    Don't use any REPAIR option as your first way of dealing with corruption. The "correct" way would be to identify *why* the corruption occured (hardware problems are very likely). A call to MS PSS would surely help you here and then restore from your last and most recent good known backup.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  6. Argyle New Member

    Yes hardware problems are likely. I would take your latest full backup and transaction log backups and restore on another healthy server. Then make one last transaction log backup and apply that one to on the new server as well.

    Reapplying the logs on a healthy server might get around your consistency errors if they where hardware or disk related. I've done this before and recovered all data.

    Other options are to try and select/insert the data from the affected tables into a new table. Then you can compare how many rows are missing. If lucky it could be just a few rows and and then those rows might be recoverd from an older backup if the data is static.

    Dropping/recreating indexes can help too if it's just index pages that are damaged.

    But I would not perform any of the above if you are not working as a DBA. I would open a case at Microsoft instead.

    In any case I would not use this server for databases until the cause of the error has been identified.
  7. Luis Martin Moderator

    What SQL version do you have?, because with 7.0 that kind of problem can occurs and not for hardware.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  8. newchick New Member

    Thank you Luis, Argyle, and Frank for your help.

    We use SQL 2000. I'll back up the db and restore it to a difference machine and will see how that goes.

    Thanks again.

    Lily
  9. Argyle New Member

    Doing the backup now might not help. You're best bet is to find a full backup that is clear from errors and the restore all transaction log backups up till now on it.
  10. newchick New Member

    You're right Argyle. I ran into the same issues with the restored one.
    The problem is I don't have trans log file kept for more than 24 hours. This means I would not restore the data.
  11. FrankKalis Moderator

    Sorry to say that, but it seems you're just about to learn the hard way that a good disaster recovery strategy is vital.
    When you say, t-logs older than 24 hours are not kept, what is your database backup interval? In any case, it really might be a good idea to open a case with Microsoft. Probably the can help you out of this without data loss, though it seems unlikely.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page