One thing I still run into quite often are SQL Server 2005 and newer databases that have their Page Verify database option set to TORN_PAGE or NONE. The most common reason for this is that an older database that was originally created in SQL Server 2000 or older was upgraded to SQL Server 2005 or newer, and the Page Verify Option was left at the older and less effective TORN_PAGE value. I also run into instances where people have changed the Page Verify database option to NONE, thinking that this would have a dramatic beneficial effect on performance (which is not true).
From BOL: “When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.”
Paul Randal talked about some of the myths around page verify here. Kendra Little wrote a good post that demonstrates how CHECKSUM reacts to corruption here.
In my opinion, all of your databases should be using CHECKSUM for their Page Verify database option. You can easily query sys.databases to find out the status of the Page Verify database option for all of your databases with this query:
-- Get value of page verify option for all databases SELECT name, page_verify_option_desc FROM sys.databases;
If you have just a few databases, it is pretty easy to run code like this for each one, to change this option:
-- T-SQL to change Page Verify option to CHECKSUM for a single database USE [master] GO ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT; GO
If you have a large number of databases that need to be changed, you can write a query to generate the ALTER DATABASE statements for you, like this:
-- 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';
After you run this query, you can copy the rows from your results grid in SSMS to a new query window, and then run your ALTER DATABASE statements when you are ready, without having to write all of the T-SQL code yourself.
Keep in mind that just changing the setting to CHECKSUM does not instantly add CHECKSUMs to your existing data pages in the database. In order for this to happen, you have to read each page into memory, make some sort of change and then write it back out to the storage subsystem. This can happen from normal INSERT/UPDATE/DELETE activity over time, or from rebuilding your indexes.
Here are some useful links on this subject:
Checksum in SQL2005
How to tell if the IO subsystem is causing corruptions?
Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?
Checksum and tempdb
Performance impact of enabling page checksum and default trace
9 thoughts on “Setting Your Page Verify Database Option to CHECKSUM”
Another way I like to check this is via Policy Based Management and my CMS – I can do all my instances in very little time. Great reminder, Glenn! I’m checking mine now, as I haven’t in quite some time 😉
That is a good way to check and monitor that. Good idea!
Hi,
I have a customer with a large database (+40TB). For some reason checksum has changed to NONE after we moved it to another server (detach / attach). I want to change that to CHECKSUM, and as I read your post, then I can do without it taking hours of work. It is first when users reads the pages –> do modifcations –> it is stored on disk again, checksum is calculated?
Does that sound correct?
Yes. You can change it at any time, since it is just a metadata change, not a “size of data change”. The checksums won’t happen until the data is read and written back to disk, which may take some time to happen with normal database usage.
Changing this option flushes the proc cache for altered database.
Glenn, I have a question even though page_verify_option_desc in sys.databases is set to NONE but when checked inside database boot page, the value for dbi_DBCCFlags value is set to 2(this is true for all databases created on SQL Server 2005 and above), so my question is are the two values different are is this above post is applicable only for database created on SQL Server 2000 or below.
I would believe what sys.databases tells you. If the page_verify_option is set to NONE, then someone explicitly changed it to that value. It is not a carry-over from the database originally being created on SQL Server 2000. The main point of my blog post is that if the page_verify_option is not set to CHECKSUM, you should change it to CHECKSUM.
Glenn, I understand now what CHECKSUM does, but once I have everything changed, what’s next? Say for instance corruption is found, do I monitor a log or can I set up a notification? what is the best avenue for me seeing the issue quickly?
Once you have the Page Verify option set to CHECKSUM, you should make sure your database backups also use the CHECKSUM option. You should also be running DBCC CHECKDB periodically to check for corruption. Corruption-related errors will show up in the SQL Server Error Log, and you can also set up SQL Server Agent Alerts to detect them when they occur.