Corrupted mdf file causing the backup job to fail

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

Corrupted mdf file causing the backup job to fail

Author

Topic

suneeth
Starting Member

2 Posts

Posted - 2014-10-23 : 07:13:12

We have a problem with one of our SQL server 2008 databases. It appears that something is corrupted in its datafile and this has caused our daily DB backup jobs to fail. The last successful backup copy taken was a month ago :(

Luckily the database is up and running and the application is able to connect to it. The only problem is it doesn't allow the db to be backed up anymore.

When I try to take a backup it gives me "Read on e:\xxx\xyz.mdf file failed. reason 15105" error.

I have stopped SQL server service and tried to manually copy the mdf and ldf files but the system doesn't allow us to copy the mdf file. It gives a "Cannot read from the disk" error

I have ran the DBCC CHECKDB command to check the errors and below are the results

1) Unable to read and latch page (1:43515) with latch type SH. 23(failed to retrieve text for this error. Reason: 15105) failed.

2) Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057595536211968 (type In-row data): Errors found in off-row data with ID 1788936192 owned by data record identified by RID = (1:44202:25)
I was thinking of running the DBCC command with REPAIR_ALLOW_DATA_LOSS but I am really scared of losing the data. If something terribly goes wrong we don't even have a recent backup copy to restore. It is a huge risk for us to have a production DB without having the ability to back it up.

What would be the best way to repair the corrupted datafile without data being lost? do you recommend any third party tools?

Is there any other way that I can backup/take a copy of this database before starting to repair?

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-23 : 07:48:14

Hi,
Thanks for not running repair_allow_data_loss. Don't believe on Third party tools they might/might not help and there is no guarantee of no data loss.
Can you run dbcc checkdb(db_name) with all_errormsgs,no_infomsgs please post output here. We would find out which all such table or index is problematic one. Perhaps Object ID 567673070, index ID 1 is the one, you can use sys.sysobjects to find out table name.

Since you dont have backup it would be advisable to create a new database on Different server. Script out all tables leaving the problematic one and run it on destination machine. Move data using import export wizard but leave problematic table. Run dbcc chekcdb on new database I hope it would come out clean.

Other way is backup database with continue_after_error see if it succeeds. If so restore that backup on new server with continue_after_error. Run checkdb with repair_allow_data_loss see how much would be data loss

Hope this helps

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

suneeth
Starting Member

2 Posts

Posted - 2014-10-23 : 09:57:36

Thank you very much for your reply Shanky.

I have tried backup with continue_after_error but it didnt work.
I have ran the DBCC command and pasted below is the result. it appears that the errors are in "Assets" and "Articles" table. unfortunately they are the most important tables in our database, we cant live without them :(

is there anything else we can do?
============================================================

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:43515) with latch type SH. 23(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057594063159296 (type LOB data): Page (1:42216) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057594063159296 (type LOB data), page (1:42216). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057594063159296 (type LOB data). The off-row data node at page (1:42216), slot 0, text ID 1788936192 is referenced by page (1:41982), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057595536211968 (type In-row data): Errors found in off-row data with ID 253820928 owned by data record identified by RID = (1:29863:3)
Msg 8929, Level 16, State 1, Line 1
Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057595536211968 (type In-row data): Errors found in off-row data with ID 1788936192 owned by data record identified by RID = (1:44202:25)
Msg 8929, Level 16, State 1, Line 1
Object ID 567673070, index ID 1, partition ID 72057595461697536, alloc unit ID 72057595536211968 (type In-row data): Errors found in off-row data with ID 253820928 owned by data record identified by RID = (1:47297:0)
CHECKDB found 0 allocation errors and 6 consistency errors in table 'Assets' (object ID 567673070).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:43515) allocated to object ID 1218103380, index ID 1, partition ID 72057596706095104, alloc unit ID 72057594050117632 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1218103380, index ID 1, partition ID 72057596706095104, alloc unit ID 72057594050117632 (type LOB data). The off-row data node at page (1:43515), slot 0, text ID 253820928 is referenced by page (1:47701), slot 11, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1218103380, index ID 1, partition ID 72057596706095104, alloc unit ID 72057596783165440 (type In-row data): Errors found in off-row data with ID 253820928 owned by data record identified by RID = (1:47701:11)
Msg 8929, Level 16, State 1, Line 1
Object ID 1218103380, index ID 1, partition ID 72057596706095104, alloc unit ID 72057596783165440 (type In-row data): Errors found in off-row data with ID 1788936192 owned by data record identified by RID = (1:48898:1)
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Articles' (object ID 1218103380).
CHECKDB found 0 allocation errors and 11 consistency errors in database 'XYZNews'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XYZNews).

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-10-28 : 13:41:58

Is your database in full recovery model - and do you have an unbroken chain of log backup file as well as an unbroken log chain?

If so, then you can restore for the known good backup, restore all transaction logs to current point in time to recover the system with all data. If not, then your only choice will be to perform the recover with the repair_allow_data_loss.

Before doing that, look up DBCC PAGE and dump all of the pages that have issues. You may be able to recover the data and re-enter it from those pages if you can actually get them dumped out.

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-11-07 : 07:14:06

Hi
I see you whole post and found that the MDF is totally corrupt so in this case you may go with external tool.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources