SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
SQL Server Message 824 - I/O error (torn page) detected during read at offset
Rachel Clements About the Author

Rachel has been working with SQL Server for ten years, building her database skills on top of her roles as both a web and Windows developer.

As well as being Technical Editor here at SQL Server Club, she is a founder and organiser of the SQL Server Club User Group Southwest, based in Bristol, UK. Her favorite activities include stepping through code, reading about the internal workings of SQL Server and drinking coffee.

Linked In Typepad Profile Rachel Clements on Facebook Rachel Clements RSS Feed Rachel Clements on Twitter
Send to a Friend Printer friendly version
SQL Server has Detected a Torn Page

If you come across the error:

"I/O error (torn page) detected during read at offset ... in file ‘C:\SQL Server\Northwind.mdf’..."

it is because SQL Server tried to access a page that had previously not been written to disk correctly. This can happen if there's a power failure or a disk or other hardware failure when the disk is being written to.

When SQL Server detected the torn page, it probably ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

Torn page detection is set as part of the database recovery options and enables SQL Server to detect incomplete I/O operations.

How a Torn Page is Detected

Although a SQL Server database page is 8Kb in size, disks I/O operations are typically performed using a 512 byte sector. As a result 16 sectors will be written to disk per 8Kb page.

If TORN_PAGE_DETECTION is ON, this causes a bit to be reversed for each 512 byte sector in the 8Kb page, as it is written to disk.

If there is problem writing to disk, such as a power failure or hardware failure, the bit will be in the wrong state and you get a torn page. This torn page will be picked up by SQL Server later on when the page is read, or if the database is recovered.

One point to note is that if the first sector of a database page is successfully written before any failure occurs, then the database page on disk will appear to be updated, although the write to disk may not have fully completed.

How to Fix Torn Pages

Check your error logs first and then restore your last backups and transaction logs. This should fix the problem, but you should also check your disks and fix any issues if there was a disk hardware failure.

If the error is limited to index pages, you may be able to rebuild the index.

How to Prevent Torn Pages

The best way to prevent torn pages is to use battery-backed disk controllers. All data will be successfully written to disk, or not written at all. There are some issues with using battery-backed controllers, but I'll cover this in a later article.

SQL Server Versions

SQL Server 2000

TORN_PAGE_DETECTION is set to be ON by default.

If you're using a controller with battery backup, you won’t need to set TORN_PAGE_DETECTION to ON as it’s not needed in this case.

If you want to find out what this is set to, use the ‘IsTornPageDetectionEnabled’ property of DATABASEPROPERTYEX, for example using the Northwind database:

SELECT DATABASEPROPERTYEX('Northwind', 'IsTornPageDetectionEnabled')

A result of 1 is TRUE and TORN_PAGE_DETECTION is ON. A result of 0 is FALSE and TORN_PAGE_DETECTION is OFF.

To set TORN_PAGE_DETECTION OFF:

ALTER DATABASE Northwind SET TORN_PAGE_DETECTION OFF

Or you can right click on the database in Enterprise Manager and choose Properties. Navigate to the Options tab and select or deselect the Torn page detection checkbox:

How to Set Torn Page Detection in SQL Server 2000

SQL Server 2005 / 2008

The default page verification option is CHECKSUM. You can change this property 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.

To change this using Management Studio right click on your relevant database and choose Properties. Select Options and then Recovery. Next to Page Verify you will see a drop down list with the three options: TORN_PAGE_DETECTION, CHECKSUM or NONE.

How to Set Torn Page Detection in SQL Server 2005 and 2008

You can use the following query to change this:

ALTER DATABASE Northwind SET PAGE_VERIFY TORN_PAGE_DETECTION

Or this to change it back to checksum verification:

ALTER DATABASE Northwind SET PAGE_VERIFY CHECKSUM

Send to a Friend Printer friendly version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form