Page verify option

Job details

Name:	Page verify option
Platform:	Sqlserver
Category:	Maintenance
Premium package:	Maintenance package
Description:	This job checks whether PAGE_VERIFY database option is set to CHECKSUM. This helps provide a high level of data-file integrity.
Long description:	This job checks whether PAGE_VERIFY database option is set to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. This helps provide a high level of data-file integrity.
Version:	1.21
Default schedule:	10 7,12 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’]

Name	Default value	Description
ignore databases	model	List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when “Page verify option” is not set to parameter value “verify option” (default CHECKSUP).
verify option	CHECKSUM	Page verify option set on database to be check by this procedure.
change settings	NO	If set to “YES“ the procedure will change the page verify option according to “verify option” parameter value.

Purpose: The purpose of this job is to validate the ‘PAGE_VERIFY‘ database option of all monitored databases, ensuring it’s set to ‘CHECKSUM’ for maintaining high data-file integrity.
Why: This job is important because enabling ‘CHECKSUM’ on the ‘PAGE_VERIFY‘ database option allows the SQL Server Database Engine to calculate a checksum for the entire content of a database page before writing it to disk, thus providing a safety net against data corruption.
Manual checking: You can manually check this setting in the database by using the following SQL commands:

SELECT name, page_verify_option_desc FROM master.sys.databases WHERE name = 'YourDatabaseName'

The job uses a stored procedure “dbw_page_verify_option_proc” to inspect each database’s current ‘PAGE_VERIFY‘ setting.
It creates a cursor to iterate through all databases that are not in the specified ignore list, not system databases, and are available for read-write operations.
During each iteration, the job compares the current setting of the database against the desired ‘CHECKSUM’ setting.
If discrepancies are found (i.e., the database’s setting does not match ‘CHECKSUM’), it logs this, and based on settings, it might alter the database’s ‘PAGE_VERIFY‘ option to the correct value.

The job has a dependency on itself, meaning it must successfully complete an iteration before moving to the next.
It includes error handling that records an error message if the procedure encounters an exception during its execution.

The job generates a report that lists all databases along with their ‘PAGE_VERIFY‘ option status.
The report is structured in a table format showing columns for database ID, Name, Status, Creation Date, Recovery Model, ‘PAGE_VERIFY‘ option status, and whether the database is in standby.

Database Field	Description
DB id	Unique identifier of the database
Name	Name of the database
Status	Current operational status of the database
Created	Creation date of the database
Recovery model	Recovery model set for the data
Page verify option	Current setting of ‘PAGE_VERIFY‘ option
Is standby	Indicates if the database is in standby mode (YES/NO)

Each database’s record is dynamically fetched and updated in the report, making the job critical for ongoing database integrity monitoring in environments with rigid data safety requirements.

The job has automated settings modification capabilities, where if enabled (change settings = ‘YES’), it will automatically adjust the ‘PAGE_VERIFY‘ option to ‘CHECKSUM’ for databases not already set to this value.
This automation aids in maintaining consistency in database configuration for integrity across all managed databases within the scope of dbWatch.