Vryjdfkk

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

Read on "D:DataFilename.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.

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.

As your mention error

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

MS SQL error 15105 is an error message which occur when a user try to create database file backup to a network shared disk or when hosting database file on a network share is not supported, and as a result such error message event take place.

How is MS SQL error 15105 caused ?

MS SQL error 15105 is caused due to following stuffs.



1)Trying to attach an .mdf file, that has not been detached properly.

2)There might not be enough free space on the destination where the user wants to keep his database backup file.

3)There might not be the permission to access the location of the backup file that is assigned in in the SQL Server service account.

4)It may also be possibility that the location of the folder in the error message is not the same instance where the user want to restore the backup. In this case it’s unlikely that the service has access to the folder.

5)If the user has copied the backup file from another computer, then the destination computer may block the file due to security reason.

6)Corruption of Windows Operating System

7)Attack of Viruses

8)Incomplete software installation

9)Excessive startup entries and registry errors.

For ref Here & Here

Just encountered the same error in SQL Server 2008R2. One of the ways is to drop and re-create the affecting index. Another way is to duplicate the table with data, indexes and trigger. Then swap and drop the affecting table.

Both methods bypass the DBCC repair.