SQL Server page verification CHECKSUM

Category: Reliability

Item:  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.

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

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published.