Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Question Unanswered: DBCC CHECKDB - Consistency Error

    Hello,

    First post here, hopefully I've chosed the correct sub-forum

    When running a DBCC CHECKDB on one of our databases the output says that there are 5 consistency errors within the table log_data

    Error message..
    Code:
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:61767) in object ID 1449108253, index ID 1, partition ID 72057639241580544, alloc unit ID 72057594116440064 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:80254) in object ID 1449108253, index ID 1, partition ID 72057639241580544, alloc unit ID 72057594116440064 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:81111) in object ID 1449108253, index ID 1, partition ID 72057639241580544, alloc unit ID 72057594116440064 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:81159) in object ID 1449108253, index ID 1, partition ID 72057639241580544, alloc unit ID 72057594116440064 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:81183) in object ID 1449108253, index ID 1, partition ID 72057639241580544, alloc unit ID 72057594116440064 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
    There are 11218 rows in 91 pages for object "user.log_data".
    DBCC CHECKDB says that 'reapir_allow_data_loss' is the minimum repair level that can be used.

    As I understand, the best solution would be to restore from backup, however upon doing a test with this, it appears this issue might have been present for quite a while and doesn't appear to be affecting anything.

    I've done a bit of testing on a copy of the database and found that running DBCC CHECKDB with repair_allow_data_loss doesn't fix the consistency errors.

    However from what I understand the error is only affecting the indexes, so after a bit of googling, I found this command..

    Code:
    ALTER INDEX ALL ON user.log_data REORGANIZE WITH (LOB_COMPACTION = ON)
    This appears to resolved the issue (on a copy of the db) and further use of DBCC CHECKDB completes without error. However I'm reluctant to run this on the live db without first understanding what it does and if there are any possible issues that could arise from using it.

    If anyone could shed any light on this I'd be grateful

    EDIT: Apologies, forgot to add that I am using SQL 2005 RTM - No SP
    Last edited by RichHumphries; 03-01-11 at 10:38. Reason: To add SQL server version

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am not sure if it has been removed or not, but you can also try DBCC UPDATEUSAGE, or at worst DBCC CHECKALLOC. UPDATEUSAGE was originally supposed to resolve these kinds of issues, but more usually with the data pages, and not LOB data. The command you did use (LOB_COMPACTION = ON), would likely have copied the LOB data to new pages, and redid the allocation size problems. To confirm if any data was lost, you would need to use DBCC PAGE to examine the problem pages, and see if you can work out which row those belong to, then check them again after the index rebuild.

Tags for this Thread

Posting Permissions

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