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:

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 the script will generate as an example:

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

More information

Microsoft –  Set the PAGE_VERIFY Database Option to CHECKSUM

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

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

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.