SQL Server Page Validation CHECKSUM

SQL Server Page Validation CHECKSUM

Category: Reliability

What is Page validation in SQL Server?

Page Validation is a database option that defines the mechanism used by SQL Server to verify the consistency of pages when they are written or read from disk. This reduces the possibility of corrupting the database and as a good practice, it should be set to CHECKSUM.

How to identify the problem?

Since SQL Server 2005, Microsoft has implemented page validation CHECKSUM by default at the SQL Server engine level. You can check it in the Database Properties window on the Options page:

How to fix it?

You can select the ” Page Verify ” property by changing it to CHECKSUM using the above interface. You can also run the following script to create a change script for all databases on the server.

Run the following query to create the change script:

use master

go

select

'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;

' Command_to_execute

from sys.databases

where page_verify_option_desc != 'checksum';

go

The output that the script will produce as an example:

ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;

ALTER DATABASE [DBA] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;