Error 823 Torn Page - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Error 823 Torn Page

Author

Topic

niceguy167
Starting Member

1 Post

Posted - 2012-05-11 : 22:54:55

I had a laptop die HDD went dead which a running sql database was opened (in program)
I had to have the hard drive recovered however all i got back was a bad database which has the torn page.
All pro software seams to be able to read my database and fix if i pay the 399.00 for the software however i was hoping i could use the built in DBCC However it seams everything i try wont work so i am hoping for some help.
Here is the error when I try to bring the database online

Msg 945, Level 14, State 2, Line 1
Database 'CompassDefaultDB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So Then I Try
DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
That give me

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

So Then I Try
ALTER DATABASE CompassDefaultDB SET EMERGENCY;

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Then

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

Msg 922, Level 14, State 1, Line 1
Database 'CompassDefaultDB' is being recovered. Waiting until recovery is finished.

It stays in this mode forever and does not seam to finish
this database is only 2MB with like 17 Rows of data i am after.
I can restart the sql server and repeat the same steps with the same results.
Is there something i am doing wrong?
any help is greatly appricated

I do have the origional corrupted database and have tried to restore and repeat but with the same results
I have also tried to bring up a database with the same name delete the mdf file and replace with me mdf file with the same basic results.
There is no backup :(
Thanks again for looking

prett
Posting Yak Master

212 Posts

Posted - 2012-05-16 : 05:28:18

Torn page error occurs 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 failure when the disk is being written to.

Check your error logs first and then restore your last backups and transaction logs. This is only solution through which you can fix torn page error.

After reading your post, I have noticed that you have already tried all possible solutions to fix this error. As you have already checked third party sql recovery software & it shows your database data then I will recommended you to go for third party tools. But you should choose it very carefully because there are lots of sql recovery software available in the market (Good & Bad).

Search on Google: SQL Database Recovery Software: http://lmgtfy.com/?q=sql+database+recovery+software
First try to select top 3 software as per your requirements.

igormalse
Starting Member

3 Posts

Posted - 2014-08-13 : 03:53:34

Check these site: http://www.sql.recoverytoolbox.com/ & download demo Recovery Toolbox for SQL Server.
Depending on how you are loading your data, you may need to break it up into smaller chunks.

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-08-21 : 06:04:07

Attempting to repair the database isn't going to help. Page 1:9 is a system object and system objects can't be repaired. You'll want to script out the tables and create a new database. Then create the tables in the new database, and copy over the data into the new database.

If you had a backup... Another option would be to restore the database to a point in time before the page became corrupt. If you have a backup from that far back you can restore that backup, then roll the transactions logs forward and you should be able to get back to a state where you have all the current data without the corruption.

granuharmot
Starting Member

31 Posts

Posted - 2014-09-23 : 05:12:41

unspammed

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-07 : 06:58:56

Hi,
>>I do have the origional corrupted database and have tried to restore and repeat but with the same results

Are you saying you have backup which is corrupt. If so can you try restoring with continue_after_error option. Unfortunately your DB seems severely corrupted unless you take help physically from some DB recovery expert(like Paul Randall) its highly unlikely you can recover it. This is why, I must say, backup is so so much important

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources