Corruption detection using checksums

Checksums are used to determine whether a database page has been modified on disk. SQL Anywhere supports two types of checksums: global checksums and write checksums.

Global checksums

Global checksums are enabled when a database is created. When you create a database with global checksums enabled, a checksum is calculated for each page just before it is written to disk. The next time the page is read from disk, the page's checksum is recalculated and compared to the checksum stored on the page. If the checksums are different, then the page has been modified on disk and an error occurs.

You can check whether a database was created with global checksums enabled by executing the following statement:

SELECT DB_PROPERTY ( 'Checksum' );

This query returns ON if global checksums are turned on; otherwise, it returns OFF.

The CHECKSUM clause of the ALTER DATABASE statement lets you disable global checksums for an existing database. Disabling checksums is not recommended. Once global checksums are disabled, they cannot be enabled. You can either use the -wc+ option to enable write checksums for the database, or you can rebuild the database with checksums enabled.

Write checksums

By default, databases created with version 10 and 11 of SQL Anywhere do not have global checksums enabled. If you start a database created with SQL Anywhere 11 on a version 12 or later database server, then by default the database server creates write checksums, which are checksums that are added to pages only when they are written to disk.

Version 12 and later databases have global checksums enabled by default. If you create a new database and disable global checksums for the database, you can enable write checksums by using the -wc+ option or the START DATABASE statement.

You can check whether a database was started with write checksums by executing the following statement:

SELECT DB_PROPERTY ( 'WriteChecksum' );

This query returns ON if checksums are enabled only when pages are written to disk (because global checksums or write checksums are enabled); otherwise, it returns OFF.

See:

Automatic write checksum creation

In the following situations, write checksums are enabled for the database, regardless of the global checksum setting that was specified when the database was created:

Critical pages The database server calculates write checksums for critical database pages in all databases, regardless of whether global checksums are enabled. These checksums are used to detect offline corruption, which can help reduce the chances of other data being corrupted as the result of a bad critical page. Because the database server calculates these checksums, if a database becomes corrupt that does not have checksums enabled, the database server shuts down with a fatal error.
As well, if you validate a database that does not have global checksums enabled, but that has a bad critical page, dbvalid can still return warnings about checksum violations.
Windows Mobile databases The database server automatically enables write checksums for databases running on Windows Mobile to help provide early detection if the database file becomes corrupt.
Databases running on some storage media When the database is running on storage media that may be less reliable, such as network or removable drives, the database server automatically enables write checksums for the database. Write checksums remain enabled as long as the database resides on such a device, and the pages are checksummed when they are written. If the database is moved to a more reliable storage device, the database server verifies the checksum for checksummed pages when they are brought into the database server cache.

See:

Validating checksums

You can validate disk pages for databases that use global or write checksums. If a database has global checksums enabled, then all database pages are validated. If a database has used only write checksums, then only pages with checksums are validated. Checksum validation requires either DBA or VALIDATE authority.

For databases with checksums enabled, a checksum is calculated for each database page and this value is stored when the page is written to disk. You can use the Validation utility (dbvalid), the VALIDATE statement, the sa_validate system procedure, or the Validate Database Wizard in Sybase Central to perform checksum validation, which consists of reading the database pages from disk and calculating the checksum for the page. If the calculated checksum does not match the stored checksum for a page, the page has been modified or corrupted while on disk or while writing to the page. If one or more pages has been corrupted, an error is returned and information about the invalid pages appears in the database server messages window.

See:

Validating a table

Discuss this page in DocCommentXchange.