Setting PAGE_VERIFY Database Option to CHECKSUM for all Databases in SQL Server
Sept 02, 2014
Introduction
When CHECKSUM is enabled as the PAGE_VERIFY database option, SQL Server Database Engine will calculate a checksum over the content of the whole page and store the value in the page header when the page is written to the disk. Whenever the page is read from the disk, the checksum is recomputed and the value is compared with the checksum value which is stored within the page header. This helps in ensuring high level of data file integrity. It is highly recommended to set PAGE_VERIFY Database Option to CHECKSUM for all the databases on SQL Server 2005 and Higher Versions.
This article includes query to identify the currently configured PAGE_VERIFY Settings for all the databases and the script to change the PAGE_VERIFY settings to CHECKSUM for database which are using either NONE or TORN_PAGE_DETECTION.
MSDN Describes TORN_PAGE_DETECTION Vs CHECKSUM Vs NONE as:
When CHECKSUM is specified, the 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 stored in the page
header.
When TORN_PAGE_DETECTION is specified, a
specific 2-bit pattern for each 512-byte sector in the
8-kilobyte (KB) database page is saved and stored in the
database page header when the page is written to disk. When the
page is read from disk, the torn bits stored in the page header
are compared to the actual page sector information.
When NONE is specified, database page writes
will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL
Server will not verify a checksum or torn page during a read
even if a CHECKSUM or TORN_PAGE_DETECTION value is present in
the page header.
Find Current PAGE_VERIFY Settings for all the Databases in SQL Server
You can identify the currently configured PAGE_VERIFY settings for all the databases in SQL Server by executing the below mentioned TSQL query.
USE Master
GO
SELECT NAME AS [DatabaseName]
, CASE
WHEN page_verify_option = 0
THEN 'NONE'
WHEN page_verify_option = 1
THEN
'TORN_PAGE_DETECTION'
WHEN page_verify_option = 2
THEN
'CHECKSUM'
END AS [Page Verify Setting]
FROM sys.databases
GO

In the above snippet you could see that PAGE_VERIFY
setting for two databases was set to be
TORN_PAGE_DETECTION and for one it was set as
NONE. However, with the help of “How
to identify if the database was upgraded from a previous version
of SQL Server” article it was identified that these
databases were upgraded from SQL Server 2000 and the PAGE_VERIFY
setting was not changed to CHECKSUM after restoring the database
on higher version of SQL Server.
Different Ways to PAGE_VERIFY Setting to CHECKSUM for Databases in SQL Server
- Change PAGE_VERIFY Setting to CHECKSUM for Databases Using TSQL Query
- Change PAGE_VERIFY Setting to CHECKSUM for Database Using SQL Server Management Studio
Change PAGE_VERIFY Setting to CHECKSUM for Database Using TSQL Query
The below script will generate the ALTER DATABASE script to set PAGE_VERIFY setting as CHECKSUM for all the databases which are not using CHECKSUM option.
Use Master
GO
SELECT
'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM WITH
NO_WAIT;'
AS [Change Page Verify Settings to Checksum]
FROM SYS.DATABASES
WHERE STATE_DESC ='ONLINE' AND page_verify_option_desc !=
'CHECKSUM'
GO
Copy and execute the generated ALTER DATABASE script in the new query window to change the PAGE_VERIFY setting to CHECKSUM for all the databases for which CHECKSUM is not set as PAGE_VERIFY option.

Change PAGE_VERIFY Setting to CHECKSUM for Database Using SQL Server Management Studio
1. Connect to SQL Server Instance Using SQL Server Management Studio
2. Expand Databases Node and Right Click the Database and choose Properties from the drop down menu.
3. In the Database Properties, Click on Options on the left panel.
4. Under Recovery, click the drop down of Page Verify as shown in the below snippet and choose CHECKSUM and click OK to save the changes.

Next Steps
- Verify the currently configured PAGE_VERIFY settings for all the databases in your environment and as a best practice always set it to use CHECKSUM as PAGE_VERIFY option for all the databases which you manage as DBAs in SQL Server 2005 and higher versions.
- The database PAGE_VERIFY CHECKSUM option can help detect database consistency problems with the system I/O path.
- For more information on Best Practices for DBAs and Developers read “SQL Server Best Practices”
References:
- Setting Database Options
- The database PAGE_VERIFY CHECKSUM option can help detect database consistency problems with the system I/O path.
- How to troubleshoot Msg 824 in SQL Server.
|
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS
FREE SQL SERVER WHITE PAPERS & E-BOOKS
FREE SQL SERVER PRODUCTS AND TOOLS
Sign up today for MyTechMantra.com Newsletter
Like Us on FACEBOOK | Follow Us ON TWITTER
LEARN MORE... |
Winners |
White Papers |
Product Reviews |
Trending News |
All Articles |
Free Tools |
Follow Us... |
![]() |