1.       SQL Server Message 824 - I/O error (torn page) detected during read at offset

SQL Server has Detected a Torn Page

If you come across the error:

"I/O error (torn page) detected during read at offset ... in file ‘C:\SQL Server\Northwind.mdf’..."

it is because SQL Server tried to access a page that had previously not been written to disk correctly. This can happen if there's a power failure or a disk or other hardware failure when the disk is being written to.

When SQL Server detected the torn page, it probably ended your connection. If the torn page was detected during database recovery, then SQL Server will mark it as SUSPECT.

Torn page detection is set as part of the database recovery options and enables SQL Server to detect incomplete I/O operations.

How a Torn Page is Detected

Although a SQL Server database page is 8Kb in size, disks I/O operations are typically performed using a 512 byte sector. As a result 16 sectors will be written to disk per 8Kb page.

If TORN_PAGE_DETECTION is ON, this causes a bit to be reversed for each 512 byte sector in the 8Kb page, as it is written to disk.

If there is problem writing to disk, such as a power failure or hardware failure, the bit will be in the wrong state and you get a torn page. This torn page will be picked up by SQL Server later on when the page is read, or if the database is recovered.

One point to note is that if the first sector of a database page is successfully written before any failure occurs, then the database page on disk will appear to be updated, although the write to disk may not have fully completed.

How to Fix Torn Pages

Check your error logs first and then restore your last backups and transaction logs. This should fix the problem, but you should also check your disks and fix any issues if there was a disk hardware failure.

If the error is limited to index pages, you may be able to rebuild the index.

How to Prevent Torn Pages

The best way to prevent torn pages is to use battery-backed disk controllers. All data will be successfully written to disk, or not written at all. There are some issues with using battery-backed controllers, but I'll cover this in a later article.

SQL Server Versions

SQL Server 2000

TORN_PAGE_DETECTION is set to be ON by default.

If you're using a controller with battery backup, you won’t need to set TORN_PAGE_DETECTION to ON as it’s not needed in this case.

If you want to find out what this is set to, use the ‘IsTornPageDetectionEnabled’ property of DATABASEPROPERTYEX, for example using the Northwind database:

SELECT DATABASEPROPERTYEX('Northwind', 'IsTornPageDetectionEnabled')

A result of 1 is TRUE and TORN_PAGE_DETECTION is ON. A result of 0 is FALSE and TORN_PAGE_DETECTION is OFF.

To set TORN_PAGE_DETECTION OFF:

ALTER DATABASE Northwind SET TORN_PAGE_DETECTION OFF

Or you can right click on the database in Enterprise Manager and choose Properties. Navigate to the Options tab and select or deselect the Torn page detection checkbox:

SQL Server 2005 / 2008

The default page verification option is CHECKSUM. You can change this property to TORN_PAGE_DETECTION or NONE.

Changing it to TORN_PAGE_DETECTION may use fewer resources but it won’t offer you as much protection as the CHECKSUM option.

To change this using Management Studio right click on your relevant database and choose Properties. Select Options and then Recovery. Next to Page Verify you will see a drop down list with the three options: TORN_PAGE_DETECTION, CHECKSUM or NONE.

You can use the following query to change this:

ALTER DATABASE Northwind SET PAGE_VERIFY TORN_PAGE_DETECTION

Or this to change it back to checksum verification:

ALTER DATABASE Northwind SET PAGE_VERIFY CHECKSUM

2.       2. Msg 945, Level 14, State 2, Line 1 Database cannot be opened due to inaccessible files or insufficient memory or disk space.

If SQL Server has a problem when it tries to open a user database or its transaction log, it marks the database state as RECOVERY_PENDING.

When you try to access the database, through a query or perhaps by trying to look at the database properties in Management Studio, you’ll see the following message:

Msg 945, Level 14, State 2, Line 1
Database ‘Northwind’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

In Management Studio run the following query to check the state of your databases:

SELECT name, state_desc FROM sys.databases

Or if you want to see the state of just the database that you’re getting the error for, as in this example I've got a problem with Northwind:

SELECT name, state_desc FROM sys.databases WHERE name = ‘Northwind’

You’ll most likely see in SQL Server Management Studio’s object browser that all your other databases are ONLINE, but the one with the error will show RECOVERY_PENDING. This will be reflected by the results of running the first query.

Take a look at your SQL Server errorlog to see further information for the database you are getting the error for. This may help you to determine the next action you need to take to fix the error.

You can view the error log in Management Studio: open the server which the problem database is located on and navigate to the Management folder; you'll find the SQL Server Logs folder under here. Expand this to see the recent logs:

Here's the entry for the problem with Northwind from my errorlog (yours may well be different):

Date 29/03/2010 11:35:03
Log SQL Server (Current - 29/03/2010 17:30:00)
Source spid13s

Message
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\northwnd.mdf for file number 1. OS error: 5(Access is denied.).

If either of the MDF or LDF are READ-ONLY or you don’t have permission to view the files then this will generate the error message (which is the problem with my Northwind database). Check if the folder(s) where the MDF and LDF were created has read/write permissions on them and their contents for the NT account under which the SQL Server service is running.

To fix the problem of insufficient disk space, which is another likely cause, you’ll need to increase the amount of space available to the database. To do this, free up space on the disk, move the database to another drive, or replace the disk with a higher capacity one.

When you’ve fixed the problem, you’ll need to get your database back ONLINE. The RECOVERY_PENDING state is a persisted state, which means it will remain in place until you change it.

This can be done by issuing the following statement:

ALTER DATABASE Northwind SET ONLINE

If no errors are reported, then your database is production ready again. However it may be wise at this point to make a full backup and run DBCC CHECKDB against the database to ensure that there are no problems with it.