If you’re SQL Server Log contains this error message:
"I/O error (torn page) detected during read at offset ... in file ‘C:\SQL Server\Northwind.mdf’..."
It means SQL Server tried to access a page that is not previously written to the disk correctly. This can happen if there's a power failure or a disk or other hardware failure in mid of the process.
The error has two conditions:
1) If SQL Server detected the torn page error message then it ended your database connection.
2) If the torn page error is detected during your database recovery process, it marked database as SUSPECT.
User set Torn page detection is to be on to enables SQL Server to detect incomplete I/O operations. it is part of the database recovery options. In SQL Server 2000, If you want to check that torn page detection is on or not you can easily check it by using ‘IsTornPageDetectionEnabled’ property of DATABASEPROPERTYEX. If result is 1, it means TORN_PAGE_DETECTION is ON. If result is 0, it means TORN_PAGE_DETECTION is OFF.
In SQL Server 2005 / 2008, there are three page verification options: TORN_PAGE_DETECTION, CHECKSUM. The default one is CHECKSUM. You can change it to TORN_PAGE_DETECTION or NONE. Changing it to TORN_PAGE_DETECTION may use fewer resources but it won’t offer you as much protection as the CHECKSUM option.
How to Fix Torn Pages
First check SQL Server error log and then restore your SQL Server database from last good backups and transaction logs. If It is not fix the issue then you should also check your disks and fix any issues if there was a disk hardware failure.
To reduce the chance of Torn Pages
The one of the best way to prevent torn pages is to use battery-backed disk controllers. Through this, all data will be successfully written to disk, or not written at all.
This is a really a frustrating error. The given solution helped you to resolve the error & you don’t need to waste your time in finding the solution.
No comments:
Post a Comment