Torn page recovery - Experiences and Best Practices

[Rudi_Wiesmayr]

Hi!

I'd like to hear your stories concerning experiences with torn pages.

- How often do you see torn pages in your SAP systems?

- Could you track down the reason?

- How did you recover?

- Is restore the only possibility?

- What do you do to reduce the possible downtime for the worst case?

Kind regards, Rudi

[Former Member]

We have been running SAP on SQL Server for over 10 years now going as far back as SAP R/3 3.0F on SQL6.5, and we have never encountered a system-down situation due to a torn page.

[Former Member]

We have had our R3, CRM and BW systems up for years running on SQL Server, probably 15+ combined, and we have never experienced a torn page in any of them. In general it is very rare to have a torn page today with most production systems using a SAN and various redundant power supplies.

You can track them using torn page detection, but it can add unneeded overhead to writes and it slows down anything trying to read that data page since it has to wait for the computation.

There is no true recover for a torn page since the data is gone, you have to use a good restore and roll forward the logs to get back to where you crashed, it won't be terribly quick but it will be all you can do.

You can reduce the downtime by having SAN hardware that will limit the possibility of it even occuring. Most of the time a torn page happens when power is interrupted during a write. If your SAN has its own power backup that is unlikely.