Database Settings

Because data pages in SQL Server (8K) and Windows 2000 or 2003 Server (512 bytes) are different sizes, it is possible during power failures, or if you are having 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 easy way to fix a database made corrupt due to a torn page, except to restore a known good backup. One of the best ways to prevent this problem is to ensure your server has a 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. There is a database option called "torn page detection" that can be turned on and off at the database level. Each database must be set separately. Keep in mind that this option does not prevent torn pages; it only tells you if you have one. Once it discovers one, your database is marked as corrupt, and you have little choice but to restore your database with your latest backup.

In SQL Server 7, 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. Maybe not by much, but if you already have a SQL Server that is maxed out, then it might make a noticeable difference. As a DBA, you must weight the pros and cons of using this option, and make the best decision for your particular situation. [7.0, 2000] Updated 2-20-2006