SQL Server torn pages
Posted on 2005-04-12
I’m receiving an 823 error message indicating I’m having a torn page. What I’m, supposed to do is recover the database. I’ve done that and I continue to get the error. If my understanding of a torn page is correct, the error occurred on the write of the data and would not be detected until the page is actually read again. If the page is not read for a while the error could have indeed occurred a while, more than the last backup, ago. I’ve gone back a month of restores and the problem still persists.
This seems suspicious to me. If I understand how the users of the database actually use the data, I don’t think the problem could have festered that long without being read. But then again the user may not be using the data how I think they are using it. Anyway, after a restore I do a DBCC CHECKDB and CHECKTABLE WITH PHYSICAL_ONLY and nothing is found. The doc say torn pages should be detected.
Question 1. How accurate is it to say that DBCC CHECKDB will detect torn pages. Is the online doc correct?
The database seems to run fine until I attempt to do the following from a nightly maintenance job.
delete from incoming where date_time < (getdate() - 45)
I receive the 823 error when the job executes. If I issue
Select * from incoming where date_time < (getdate() - 45)
It returns rows and makes me scratch my head in wonderment because it appears to there is no torn page on a select * but there is on a delete.
Question 2. Could someone explain why this may be the case?
Question 3. If this is an active physical intermittent device problem where would I go to detect an actual device issue?
Question 4. Since I’ve exhausted my month of nightly backups and the problem still occurs, could a way around this problem be to
1 turn torn page detection off,
2 create a temp table
3 copy into the temp table all the data that is more recent then 45 days.
4 drop and recreate the old table
5 move the data from the temp table to the new table.
I realize some data may be lost, but I think I will have been able to retain some of the most recent data which will be used in users report generation.
Thanks for your time and effort.