Maintaining Database Integrity

TORN PAGE

Data pages are 8 kilobytes(KB) in size, but SQL Server divides a page into 16 blocks of 512 bytes a piece when performing write operations. 

If SQL Server begins writing blocks on a page and the disk system fails in the middle of the write process, only a portion of  page is written successfully, producing a problem called a torn page. 

Database Integrity Checks

CHECKSUM OR TORN PAGE DETECTION.

Databases have an option called PAGE_VERIFY. The page verification can be set to either TORN_PAGE_DETECTION or CHECKSUM. 

Note
 The PAGE_VERIFY TORN_PAGE_DETECTION option exists for backwards compatibility and should not be used. 

When the PAGE_VERIFY CHECKSUM option is enabled, SQL Server calculates a checksum for the page prior to the write. Each time a page is read off disk, a checksum is recalculated and compared to the checksum written to the page. If the checksums do not match, the page has been corrupted.

Important:

DBA should enable the PAGE_VERIFY CHECKSUM option on every production database.

CHECK INTEGRITY:

You can force SQL Server to read every page from disk and check the integrity by executing the DBCC CHECKDB command. 

Examples of DBCC CHECKDB is: 

 
Check the current database.DBCC CHECKDB-- Check the pubs database without nonclustered indexes.DBCC CHECKDB ('pubs', NOINDEX) 

When DBCC CHECKDB is executed, SQL Server performs all the following actions: 

·         Checks page allocation within the database 

·         Checks the structural integrity of all tables and indexed views 

·         Calculates a checksum for every data and index page to compare against the stored checksum 

·         Validates the contents of every indexed view 

·         Checks the database catalog. 

Integrity Errors

If an error occurs , it should be fixed.If an integrity error is found in       Index , drop and recreate the index.Table, use most recent backups to repair the damaged page.

     Validates Service Broker data within the database 

To accomplish these checks, DBCC CHECKDB executes the following commands: 

DBCC CHECKALLOC, to check the page allocation of the database 

DBCC CHECKCATALOG, to check the database catalog 

DBCC CHECKTABLE, for each table and view in the database to check the structural integrity .