SQL Server Performance Forum – Threads Archive
Torn page detected
I have a data warehouse, W2K sp 3 SQL2K sp2 which is loaded in its entirety overnight with data which is bulk inserted from another server. This data is transformed and processed from a staging database to a Mart, then Analysis Services cubes are built on it. Problem! Users, champing at the bit, are getting hot under the collar becuz the load has failed two nights in a row. It takes 9.5 hours, so isn’t re-runnable during the working day. First failure was related to a rogue backup which failed to complete and left an open transaction, therefore filling the t-log of one of the dbs and causing it fail. Easy to fix. So I re-ran it last night and it failed with Error: 823, Severity: 24, State: 2I/O error (torn page) detected during read at offset 0x00000170db8000 in file ‘F:sqldataMAP_StagingMAP_Staging_Data.MDF’.. DBCC CHECKDB() on this database is clean. No hardware errors, disk array is fine. Question:- should I turn off torn page detection? Is it doing me any good having it on?
These databases are dbcc’d every day and their tables are truncated, re-loaded and freshly indexed every night. It strikes me that leaving it on is not helping me at all. Anyone know if there is any associated danger? I am waiting for my primary support provider to get back to me…. TIA Tom Pullen
DBA, Oxfam GB
Check the accessibility and condition of the device in question.
Run hardware diagnostics and correct problems, if possible. And in general Torn_Page detection option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. So if you are confident to track down the H/w malfunctions and dis-similarities then you can switch off this option by using SP_DBOPTION. BTW are you using battery-backed disk caches?
And I would propose to upgrade to SP3a on SQL also, though if its not faced to internet. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I am about to go sp3a, but it’s not internet-facing. We have battery backed-cache and there is no hardare malfunction. Our primary support provider has advised us to turn off torn page detection. Thanks for your reply. Tom Pullen
DBA, Oxfam GB
Then make sure to watch regular events from event viewer for any malfuncion of H/w. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Turning of Torn page Detection is not advisable as it indicates an Disk i/o problem that can lead to a disaster. I would try turning off the battery backup and see if this solves the problem. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
IF you have optimum level of backups then you can set off this option, and that is the reason its suggested to refer thru event viewer and any log/tool that can give information on H/w resource on a regular basis.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
yes. this data is loaded in its entirety every night… all tables are truncated – so the whole thing is not at risk. if corruption occurs as a result of undetected torn pages, i can, in the event of a crisis, drop and recreate the db from scratch if necessary. Tom Pullen
DBA, Oxfam GB
]]>