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:
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 mastergoselect'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
' Command_to_executefrom sys.databaseswhere 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;
