Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: Repair_allow_data_loss

    Hi all, Just wanted to know whether, DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS , will show how much of data we lost, in case any is lost. Or will it ever show, that data is lost.

    I have got about 5 million rows and this database is new to me.. and in case if any data is lost during the repair it would be difficult for me to analyze manually.

    Can anybody suggest the best way to analyze the data lost. My count(*) on the corrupted table is not working, so even if data loss after REPAIR_ALLOW_DATA_LOSS i wont be able to analyze how much of data is lost.

    Thanks, BA Posts

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You will not get a report of what data will be removed. The corruption is SQL Server being unable to tell either where the correct page is, or whether any of the data on it is good. If it can not tell what or where the data is, how can it tell you what it will delete?

    now, if the corruption is all in indexes (indid > 1), then you can simply drop and rebuild the affected indexes, and you should have no data loss. If the corruption is on indid 0 or 1, then you will lose data, and should probably consider going back to your last known good backup.

  3. #3
    Join Date
    Oct 2009
    Posts
    4

    Thanks

    Thank you for responding fast.

    I am having no allocation errors but its 2 consistency errors.
    2 consistency errors each for 2 tables. I tried dropping indexes and recreating but couldnt. One of the table there is no clustered index.

    Could you also tell me what indid= 0 stands for.

  4. #4
    Join Date
    Oct 2009
    Posts
    4

    Error Details

    Thanks all for responding.
    Here is the results of the DBCC CHECKTABLE on the 2 tables I have issues with:

    dbcc checktable('Web_claim')

    Server: Msg 8928, Level 16, State 1, Line 1
    Object ID 294292108, index ID 0: Page (1:1834754) could not be processed. See other errors for details.
    Server: Msg 8944, Level 16, State 1, Line 1
    Table error: Object ID 294292108, index ID 0, page (1:1834754), row 62. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 154 and 53.
    DBCC results for 'Web_Claim'.
    There are 918513 rows in 6767 pages for object 'Web_Claim'.
    CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Web_Claim' (object ID 294292108).
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Prod.dbo.Web_Claim ).

    dbcc checktable('Claim_item')

    Server: Msg 8928, Level 16, State 1, Line 1
    Object ID 1618820829, index ID 0: Page (1:1835914) could not be processed. See other errors for details.
    Server: Msg 8944, Level 16, State 1, Line 1
    Table error: Object ID 1618820829, index ID 0, page (1:1835914), row 8. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 1263 and 398.
    DBCC results for 'Claim_Item'.
    There are 5402810 rows in 314154 pages for object 'Claim_Item'.
    CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Claim_Item' (object ID 1618820829).
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Prod.dbo.Claim_Item ).



    And also one more thing

    SELECT COUNT(*) FROM Web_CLAIM
    --918,646 (DBCC 918513 , so i guess a loss of 133 rows)

    SELECT COUNT(*) FROM Claim_item
    --5,402,828 (DBCC 5402810, so i guess a loss of 8 rows)

    Now I know the count of rows I might lose, but can I avoid losing it or could i in anyway identify the rows which I am gonna lose.

    And we cant restore from the recent backups because this corruption happened 2 months back.
    Last edited by baposts; 10-13-09 at 15:44.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    indid = 0 means the table itself. Since the table data is corrupt, you can not read the table, to recreate the index. That is why you can only drop and rebuild indexes to fix this IF indid > 1. There will be lost data, if you run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.

  6. #6
    Join Date
    Oct 2009
    Posts
    4

    Repair

    Thank you.
    Ok now assume I repaired this 133 and 18 rows, and if I have a good backup which is 4 months old, is there a good algorithm to identify the rows I lost. One major issue is that these 2 tables dont have an identity column.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •