Answered by:
DBCC CHECKDB Errors

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.
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