locked
DBCC CHECKDB Errors RRS feed

  • Question

  •  

    SQL 2005
    I ran the following:

    DBCC CHECKDB (‘DATABASENAME’)

    Errors are produced

    DBCC CHECKDB ('DATABASENAME')

     

    DBCC results for 'AllDocStreams'.

    Msg 8914, Level 16, State 1, Line 1

    Incorrect PFS free space information for page (1:26839) in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 71788018805309440 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

     

    I wanted to view the PAGE by doing

    dbcc traceon(3604)

    DBCC PAGE (1,26839,1)

    (But keeps telling me there is no page)

     

    So i tried
    DBCC IND('TEST','AllDocStreams',1)

    Found the PagePID of 26839 then i found IAMFID so i tried

    DBCC PAGE (1,1577,3)

    still no page

     

    I was trying to find the PCT FULL on the page?  but can't find the page its talking about.

     

    I can do a REPAIR_WITH_DATA_LOSS but i wanted to figure out what it is actually repairing and what the message is really telling me

     

    Thanks

    Thursday, January 24, 2008 3:48 PM

Answers

  • The error is pretty benign - it's not an actual corruption. It's saying that the PFS (Page Free Space) entry for that page has the wrong amount of free space noted - 100% instead of 0%. All this means is that the page may be picked up be a free-space scanner looking to insert data into a text page and it will find the page is actually full and move on to the next page.

     

    All repair will do is set the correct free space setting, but you'll need to take the database into single-user mode (essentially offline) to do it.

     

    These errors occured reasonably frequently on SQL 2000 as the algorithm to keep track of free space (only tracked for heap and text pages) had some holes in it. They should be fixed in 2005. Is this database recently upgraded from 2000, or is this the first time CHECKDB has been run since it was upgraded?

     

    To use DBCC PAGE, you need to specify the database name too:

     

    DBCC PAGE ('DATABASENAME', 1, 26839, 1)

     

    should do it for you.

     

    Thanks

     

    Friday, January 25, 2008 5:50 AM

All replies

  • The error is pretty benign - it's not an actual corruption. It's saying that the PFS (Page Free Space) entry for that page has the wrong amount of free space noted - 100% instead of 0%. All this means is that the page may be picked up be a free-space scanner looking to insert data into a text page and it will find the page is actually full and move on to the next page.

     

    All repair will do is set the correct free space setting, but you'll need to take the database into single-user mode (essentially offline) to do it.

     

    These errors occured reasonably frequently on SQL 2000 as the algorithm to keep track of free space (only tracked for heap and text pages) had some holes in it. They should be fixed in 2005. Is this database recently upgraded from 2000, or is this the first time CHECKDB has been run since it was upgraded?

     

    To use DBCC PAGE, you need to specify the database name too:

     

    DBCC PAGE ('DATABASENAME', 1, 26839, 1)

     

    should do it for you.

     

    Thanks

     

    Friday, January 25, 2008 5:50 AM
  • Thanks for replying on this thread too Paul, i usually not get too much response on this forum so i am appreciating your comments.

     

    Yes the database was restored from SQL 2000 to SQL 2005 after the restore, i did the following:

    in this order

     

    Set compatibility mode to 90

    Set CHECKSUM on database

     

    DBCC CHECKDB WITH DATA_PURITY

    update statistics with full  scan

    rebuild indexes

    dbcc updateusage with count rows

     

    Everything reported correctly

     

    Then each night ran

    DBCC CHECKDB WITH DATA_PURITY

    rebuild indexes

     

    Been running the nightly scripts for two weeks then found the Errors

     

    Got the page up this time

     

    BUFFER:

     

    BUF @0x03C0D6AC

    bpage = 0x79FA2000 bhash = 0x00000000 bpageno = (1:259063)

    bdbid = 48 breferences = 0 bUse1 = 43725

    bstat = 0xc00009 blog = 0x59ca2159 bnext = 0x00000000

    PAGE HEADER:

     

    Page @0x79FA2000

    m_pageId = (1:259063) m_headerVersion = 1 m_type = 3

    m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x20

    m_objId (AllocUnitId.idObj) = 181575685 m_indexId (AllocUnitId.idInd) = 255

    Metadata: AllocUnitId = 71788018805309440

    Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1

    Metadata: ObjectId = 181575685 m_prevPage = (0:0) m_nextPage = (0:0)

    pminlen = 0 m_slotCnt = 0 m_freeCnt = 8096

    m_freeData = 96 m_reservedCnt = 0 m_lsn = (4300:941:351)

    m_xactReserved = 0 m_xdesId = (0:106851) m_ghostRecCnt = 0

    m_tornBits = 1

    Allocation Status

    GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

    PFS (1:258816) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:6) = NOT CHANGED

    ML (1:7) = NOT MIN_LOGGED

    DATA:

     

     

    So running the REPAIR_WITH_DATA_LOSS in this scenairo would only be fixing the PFS size so if this was in production this would be acceptable.

     

    So would this value

    PFS (1:258816) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:6) = NOT CHANGED

    be the line that would change after running the REPAIR.

     

    I always like to see what the command is trying to do before running it.

     

     

    Thank you
    Saturday, January 26, 2008 2:27 PM
  • Correct.

     

    You can always find out what CHECKDB errors mean by looking on technet. I did the following search on Google 'sql server error 8914' and top link was http://technet.microsoft.com/en-us/library/aa226363(SQL.80).aspx

     

    These error explanations are based on internal documents I wrote when I owned CHECKDB that details what all the errors mean. Most are the same between 2000 and 2005 (the 2005 set hasn't all been published by MS yet :-(

     

    Thanks

    Saturday, January 26, 2008 11:42 PM