SQL Server 2000 torn_page_detection error (error 823)

 

2010-08-05 09:21:51. 31 spid11 error: 823, severity: 24, status: 2
2010-08-05 09:21:51. 31 spid11 I/O error (torn page) detected during read at offset 0x00000000496a000 in file 'd:/Microsoft SQL Server/MSSQL/data/xxxxx_data.mdf '.

========================================================== ====================

Description & solution:

 

Torn_page_detection:
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

When set to on, this option causes a bit to be reversed for each
512-byte sector in an 8-kilobyte (Kb) database page when the page is
Written to disk. If a bit is in the wrong state when the page is later
Read by SQL Server, the page was written incorrectly; a torn page is
Detected. Torn pages are usually detected during recovery because any
Page that was written incorrectly is likely to be read by recovery.

Although SQL Server database pages are 8 KB, disks perform I/O
Operations using a 512-byte sector. Therefore, 16 sectors are written
Per database page. A torn page can occur if the system fails (
Example, due to power failure) between the time the Operating System
Writes The first 512-byte sector to disk and the completion of the 8-kb
I/O operation. If the first sector of a database page is successfully
Written before the failure, the database page on disk will appear
Updated, although it may not have succeeded.

Note using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.

If a torn page is detected, an I/O error is raised and the connection is
Killed. If the torn page is detected during recovery, the database is
Also marked suspect. The database backup shocould be restored, and any
Transaction Log backups applied, because it is physically inconsistent.

By default, torn_page_detection is on.