Recently I was approached to help fix an accounting server problem. Now keep in mind I wasn’t managing this server and hadn’t touched it before. The DB was on SQL Server 2000. After attempting to reindex some tables it was apparent there were some serious issues. Looking in the server’s Event Viewer, the Application Log showed the following:

Error: 823, Severity: 24, State: 2 I/O error (torn page) detected during read at offset 0x00000z030b0000 in file ‘D:\dbFileName.MDF’

This means there were some corruption issues. Reading a lot of posts on the web, people said it wasn’t possible to fix and should just restore from the last backup. Now suppose there wasn’t a good backup to restore from… let’s just PRETEND that could happen.

Here is what I did to FIX the server. There were 944 tables on their database. I detached the DB and copied it to a SQL 2005 server and re-attached. In management studio I did a properties on the DB and went to options. I changed the Compatibility level to SQL Server 2005 (90). Then right clicked the DB, Reports, Standard Reports, Disk Usage by Top Tables. This gave me a report of the tables most used. Turns out There were only about 25 tables with a significant amount of data.

I then did a DBCC CHECKTABLE (“tablename”) on the top 25 and found out which ones had issues. I had 21 that needed to be fixed. Now off of to fix them.

Back on the SQL 2000 server I right clicked on the DB, All Tasks, Generate SQL Script, Options, and checked everything, General Tab, Preview. I copied this to a separate text file and saved it for later.

I renamed the corrupt database to something else. I did this by detaching the DB, and reattached using a new name. I then took my DB script and ran select portions to recreate the shell for the clean DB with the original name. I right clicked the corrupt database and selected All Tasks, export data. The trick is to copy all the data and objects from the old database to the new one. On the specify Table Copy or Query screen choose the 3rd option of Copy objects and data between SQL Server databases. This will take all the data and indexes and put it into the new database. After everything is copied and verified, you can eventually detach the corrupt DB and delete the files.

It’s been a week now and the accounting system is working great.

What causes torn page errors?
If the TORN_PAGE_DETECTION option is turned on, it flips a bit for each 512-byte sector written to disk. This allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. A torn page can occur if the system crashes between the time the operating system writes the first 512-byte sector to the disk and the completion of the I/O operation. If the actual value read from disk is different than what’s stored in the page header, it results in a torn page error.

If you are using SQL 2005 you can run the following query which shows pages suspected as being bad with an 823 or 824 error:

USE msdb
GO
SELECT * FROM dbo.suspect_pages