SQL Server page verification CHECKSUM

Page Verification not optimal

What is Page verify in SQL Server?

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

By the way, this check is a part of our SQL Server Health Check.

How to identify the issue?

Since SQL Server 2005, Microsoft implements page verification CHECKSUM by default on the SQL Server engine level. You can check it on Database Properties window on the Options page:

Figure 1 – Page Verification option on SQL Server.

How to fix it?

You can choose it using the interface above, just changing the property “Page Verify” to CHECKSUM. Also, you may run the script below to generate the change script to all databases on the server.

Run the query below to generate the change script:

  1. use master
  2. go
  3. select
  4. 'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
  5. ' Command_to_execute
  6. from sys.databases
  7. where page_verify_option_desc != 'checksum';
  8. go

The output the script will generate as an example:

  1. ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
  2. ALTER DATABASE [DBA] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;

More information

Microsoft –  Set the PAGE_VERIFY Database Option to CHECKSUM

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *