I have several databases where PAGE_VERIFY is set to NONE and I recently learned that this is a less than ideal setting. From what I've read, the best practice (and default value) is to use CHECKSUM or, failing that, TORN_PAGE_DETECTION; apparently there's some overhead associated with those settings, but it's usually not enough to outweigh the benefits (please correct me if I've misunderstood the basics).
I also read that the PAGE_VERIFY option is new to SQL 2005 and that it uses a different mechanism than the old TORN_PAGE_DETECTION db option in SQL 2000 (and prior). When I look at our databases that have PAGE_VERIFY = NONE, they were all upgraded from SQL 2000 and they all have a compatibility level of 80. However, we also have some databases that I know were upgraded from SQL 2000 that use compatibilily level 90 and do not have PAGE_VERIFY set to NONE. I know that using compatability mode 80 is not exactly like running SQL 2000, but I couldn't determine if this is one of the things it affects, or not.
So my question essentially boils down to this: do I need to change the compatibility mode to 90 when I change the PAGE_VERIFY db option? Are there any "gotchas" or other factors I should consider when changing this option for an existing db (as opposed to when creating a new db)?
the compatibility level is really misunderstood by a lot of people. as you've said, using compatibility level 80 doesn't mean you're running the database in sql server 2000. it only says that the T-SQL commands that are new to the version your running (2005 or 2008) should be ignored. this is specially good if you have any user object named with a system name.. for instance, you can have a table called WITH in the database and your queries will work if your using compatibility level 80, in sql 2005 and 2008, WITH is a reserved word...
said that.. the PAGE_VERIFY option can be set to any of the possible values, even if the database is running in compatibility level 80. 'cause the internal engine will behavior just like if you were using a database level 90 or 100. the only difference is in T-SQL interpretation..
when you upgrade your database from SQL2000 to 2005/2008, SQL Server does not change the page_verify option. you should change this manually, that's the reason you have so many databases running without any page_verify (which is really not a good thing)
answered Oct 23 '09 at 03:34 PM