I/O error (torn page)

[ranijee]

Hi, I have sql2000. I run my olaps on monthly basis. Last week, one of the hard disk was corrupted. I swapped the faulty hard disk with a new one (mine is configured Raid 5).
When I try to run the DTS/Olap again, it gives error -

Data source provider error:I/O error (torn page) detected during read at offset 0*00000129b58000 in file 'd:\program files\Microsoft SQL Server\MSSQL$Info\Work.mdf'.;HY00 Record=23404086.

I search for the info in the internet and run dbcc checkdb. Below is part of the error results...

CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 2139192170)' (object ID 2139192170).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'Work'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Work).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

have anyone ever experienced this? Appreciate the help..
thanks

 

[JayKusch]

Hope you have a good backup or are prepared to loose data.

Torn_Page_Detection

Because data pages in SQL Server (8K) and NT Server or Windows Server (512 bytes) are different sizes, it is possible during power failures, or if you are have disk driver or physical disk problems, for your database to become corrupted.

Here's why. Every time the operating system writes an 8K SQL Server data page to disk, it must break up the data into multiple 512 byte pages. After the first 512 byte of data is written, SQL Server assumes that the entire 8K has been written to disk successfully. So if the power should go out before all of the 512 byte pages that make up the 8K SQL Server page are written, then SQL Server does not know what has happened. This is known as a torn page.

As you can imagine, this corrupts the data page, and in effect makes your entire database corrupt. There is no way to fix a database made corrupt due to a torn page, except by restoring a known good backup. One of the best ways to prevent this problem is to ensure your server has battery backup. But this does not prevent all problems, because a defective disk driver can also cause similar problems (I have seen this.)

If you are worried about getting torn pages in your SQL Server databases, you can have SQL Server tell you if they occur (although it can't prevent them or fix them after they have occurred). There is a database option called "torn page detection" that can be turned on and off at the database level. If this option has been turned on, and if a torn page is discovered, the database is marked as corrupt and you have little choice but to restore your database with your latest backup.

In SQL Server 7.0, this option is turned off by default, and you must turn it on for every database you want it on for. In SQL Server 2000, this option is turned on by default for all databases.

So what's the big deal, why not just turn it on and be safe? The problem is that turning this feature on hurts SQL Server's performance. Not much mind you, but if you already have a SQL Server that is maxed out, then it might make a noticeable difference, and you may want to keep this option turned off. As a DBA, you must weight the pros and cons of using this option, and make the best decision for your particular situation.



Thanks

J. Kusch