Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Corruption and PAGE_VERIFY CHECKSUM Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 9:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:08 AM
Points: 14, Visits: 70
Hi,

We've been having sporadic cases of corruption. The tables vary but the message is consistent:

Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (5:537) in database 47 failed. It belongs to allocation unit 72057602025521152 not to 72057602063532032.


When I inspect the tables using DBCC IND and DBCC PAGE I can see that, for example, page 5:537 does have an incorrect allocation unit in it's header (as compared to the IAM page).

Yes, most sources state that most corruption is caused by I/O sub-system, but in our situation we need to prove this.


On this ms page http://support.microsoft.com/kb/2015739 it reads:

Ensure you have the PAGE_VERIFY=CHECKSUM database option turned on. While it is possible to still encounter a Msg 605 error with checksum enabled (Ex. "scribbler" problem or incorrect page provided from I/O system), enabling checksum can provide definitive proof that the page was modified incorrectly within the I/O system.


Could anyone explain to me how this provides definitive proof? Definitive proof would be very helpful in our case!

Thanks
Post #1462705
Posted Wednesday, June 12, 2013 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Do you have page verify set to CheckSum?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1462722
Posted Wednesday, June 12, 2013 10:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:08 AM
Points: 14, Visits: 70
Yes I've verified that is the case.

I can't find any 824 errors in the logs though.
Post #1462725
Posted Wednesday, June 12, 2013 10:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Was the database created in SQL 2005/2008?
If you check the page (via DBCC Page), what's the value for the m_flagBits setting?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1462729
Posted Wednesday, June 12, 2013 10:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:08 AM
Points: 14, Visits: 70
m_flagBits = 0x220
Post #1462732
Posted Wednesday, June 12, 2013 10:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:08 AM
Points: 14, Visits: 70
Sorry, created in SQL 2008 R2...
Post #1462734
Posted Wednesday, June 12, 2013 10:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Apparently there are some known bugs that cause this (error 605 with no 824). I was told to search google.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1462736
Posted Wednesday, June 12, 2013 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 3:08 AM
Points: 14, Visits: 70
Interesting, thanks.

I have seen the bug about inserting in to a Temp table but pretty much ruled that out.

I'll see what I can find.

Post #1462738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse