Setting PAGE_VERIFY Database Option to CHECKSUM for all Databases in SQL Server

Connect With MyTechMantra.com
Follow @MyTechMantra

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

Query to find Page_Verify Settings of all the databases

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.

ALTER DATABASE SET PAGE_VERIFY CHECKSUM WITH NOWAIT

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.

Change PAGE_VERIFY Setting to CHECKSUM for Database Using SQL Server Management Studio

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”