SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Error 823 Torn Page
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

niceguy167
Starting Member

1 Posts

Posted - 05/11/2012 :  22:54:55  Show Profile  Reply with Quote
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

USA
205 Posts

Posted - 05/16/2012 :  05:28:18  Show Profile  Visit prett's Homepage  Reply with Quote
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.
Go to Top of Page

igormalse
Starting Member

3 Posts

Posted - 08/13/2014 :  03:53:34  Show Profile  Reply with Quote

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.
Go to Top of Page

Lincolnburrows
Starting Member

43 Posts

Posted - 08/21/2014 :  06:04:07  Show Profile  Reply with Quote
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.

Go to Top of Page

granuharmot
Starting Member

31 Posts

Posted - 09/23/2014 :  05:12:41  Show Profile  Reply with Quote
unspammed
Go to Top of Page

Shanky
Yak Posting Veteran

United Kingdom
84 Posts

Posted - 10/07/2014 :  06:58:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000