Have an older but large db (6tb on the san) that still has its page verification option set to TORN_PAGE_DETECTION. How do you apply the new checksum to all existing pages when changing page verification to checksum mode? Is there any big overhead to be wary of when making this change to page verification mode? Does this need to be done during a long maintenance window?
SQL 2014 Current DB Compat Level = 120
It won’t happen all at once. Each page will get changed independently when it is modified through some other action (e.g. insert or update).
This was done precisely so that this change would not be a size-of-data operation.
Of course you could force it to be immediate for a given table by rebuilding, but the big cost there would be the rebuild, since you’re writing all of the page contents anyway. Switching those pages to checksum as a part of the process is an almost-zero-cost add-on, like how much gas mileage you lose if you add a kid to the carpool.
