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

Answer 1

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

 

Answer 2

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

Answer 3

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