Sql-server – Read on MDF file failed with reason 15105

log-shippingsql serversql-server-2008-r2

The log shipping backup job for one of our databases is failing with the error message:

Read on "D:\Data\Filename.mdf" failed: 1(failed to retrieve text for this error. Reason: 15105)

Attempting to run the job out of schedule (to see if this gleans any additional data) results in a failure with the same message.

What is the cause of this message and how would I resolve it?

Select @@VERSION:

Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64) Mar 19 2015
12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit)
on Windows NT 6.1 (Build 7601: Service Pack 1)

DB Compatibility Level: SQL Server 2008 (100)

Recovery model: Bulk-logged

Results of DBCC CHECKDB:

DBCC CHECKDB ('DatabaseName') WITH PHYSICAL_ONLY reports:

DBCC results for 'DatabaseName'.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112497) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112498) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112499) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112500) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112497) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112498) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112499) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112500) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'schemaname.tablename' (object ID 557349150).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'DatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Best Answer

CHECKDB found 0 allocation errors and 4 consistency errors in table 'schemaname.tablename' (object ID 557349150). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).

There is corruption in your database and because of this the backup job is failing, I dont think account permission has anything to do with your problem. I can also see the corruption is bit severe and checkdb aborted some internal checks.

Table error: page (1:2112497) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data)

The corruption points out to table having clustered index so dropping and recreating index on the table is not going to help

Solution:

The solution I see is restore the database from clean backup. If you have doubt with backup first run restore verifyonly and see if it returns backup set is valid, if not you would have to try other backup set.

You can also try to backup the database with continue after error clause and see if you get lucky. If you are them restore the backup on other server with same clause and run dbcc checkdb (db_name) repair_allow_data_loss to check how much data you can loose. Remember repair_allow_data_loss should be LAST resort. Use ONLY if you have no possible means to recovery data.

You can also try some third party data recovery solutions. I have not tried any so I cannot recommend one.

Related Question