when/how do you determine PAGE_VERIFY for  a database?

25112
25112 used Ask the Experts™
on
for a DB that you inherited or you designed, what factors/considerations will you use/see to determine what PAGE_VERIFY setting to set for the user database?

thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
you should use CHECKSUM, use the below query to generate the alter script
-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db
WHERE db.page_verify_option_desc <> N'CHECKSUM';

Remember, the actual conversion for the existing pages happens when sql server actually reads that page, this could be an INSERT /UPDATE/ DELETE operation

Author

Commented:
>>you should use CHECKSUM,
is that a best practice standard.. would you want to keep any of the other options at any other scenarios/situations?

What/where is a new database getting its PAGE_VERIFY on creation? from MODEL database?
AneeshDatabase Consultant
Top Expert 2009
Commented:
>is that a best practice standard.. would you want to keep any of the other options at any other scenarios/situations?
I always set all my dbs to checksum

>What/where is a new database getting its PAGE_VERIFY on creation? from MODEL database?
Yup, all the default settings inherit from Model

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial