June 15, 2009 at 8:17 am
Page verify none means that there's no way for SQL to tell that the page has been damaged by the IO system or by partial writes. It means that any corruption that doesn't damage the page's structure will go completely unnoticed. It's a rather dangerous setting to have and it's never been the default (as far as I know)
Page verify torn page mean that when SQL requests that the page be written to disk it includes a bit pattern on each sector. With this it's possible to tell if only part of the page was written to disk, say if there's a power failure and the write cache isn't battery-backed. It can detect limited forms of corruption but there's still quite a few (where only a few bytes get changed) that will not be noticed unless they damage the page or row structure. This was default in SQL 2000 and earlier
Page verify checksum means that SQL computes a checksum of the entire page's contents before it's written to disk and writes that checksum as part of the page header. The checksum is recalculated and compared when the page is read back. This means that any form of damage to the page will be detected as soon as the page is read back. This is only available from SQL 2005 onwards and is default for databases created in 2005 or above.
One of my DB is currently set to Page Verify - None, which makes me think it could cause corruption?
It won't cause corruption. There's no setting within SQL that can cause corruption, corruption's typically (99%+) the result of a misbehaving IO subsystem. However with no page verification it's possible that corruption will occur but never be detected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2009 at 8:25 am
Thanks a lot Gail, this is the best explanation I had of page verify settings. Once again I learn a lot from reading your posts.
I will change my DB To page verify CheckSum right now, since it could cause problems.
Thanks again,
Cheers,
J-F
November 5, 2009 at 6:24 am
Hi, I'm pretty much in the same boat as Keith, i.e. v.new to this game. I've got the same errors appearing during integrity check immediately after applying sp3 to sqlserver 2005. Would you suggest same fix as for Keith? I've run repair_fast and repair_rebuild to no effect. Hope you can help. Regards, Jules.
November 5, 2009 at 9:17 am
julia.streatfield (11/5/2009)
Hi, I'm pretty much in the same boat as Keith, i.e. v.new to this game. I've got the same errors appearing during integrity check immediately after applying sp3 to sqlserver 2005. Would you suggest same fix as for Keith? I've run repair_fast and repair_rebuild to no effect. Hope you can help. Regards, Jules.
Please start a new thread and post the output of the following in that thread.
DBCC CheckDB (<Database name>) WITH No_Infomsgs, All_ErrorMsgs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 9:42 am
Hi Gail I've created a new question (I think) but had problem with the 'tags' bit - I think I get it now but that's too late! Tag I put it under was checkdb if that helps you find it. Let me know if it's lost in space and I'll try to do it again. Doh.
Cheers, Jules
November 5, 2009 at 10:07 am
Where? The only posts I see from you are these two. Link?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 10:16 am
Hi Gail I've posted a new topic in this forum you should be able to see this one. Sorry for being a pain. Jules.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply