2013-08-22

Backup failed - nonrecoverable I/O

On a SQL 2008 SP2 (10.0.4000) I take database backup with a Maintenance Plan. This morning I had a failed database backup:
Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
No nice :-(

When I looked in the log file generated by the Maintenance Plan, the error message was:
Task start: 2011-01-18T22:14:00.
Task end: 2011-01-18T22:14:01.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [thedatabase] TO  DISK = N'T:\\Backup\\th..." failed with the following error: "A nonrecoverable I/O error occurred on file "T:\\Backup\\thedatabase_backup_2011_01_18_221400_5137140.bak:" 112(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


When I looked in the Windows System Log, there was no relevant entries.
But looking at the drive and the free space I saw that there was not space enough for the next backup file.
After a cleanup and manual execution of the Maintenance Plan everything was green.

The amount of free space can be looked up with PowerShell like this:
Get-WmiObject -Query "SELECT FreeSpace, Size FROM Win32_LogicalDisk WHERE DeviceID = 'T:'" -ComputerName SANDY.sqladmin.lan |
Format-Table @{Name='Freespace (GB)';Expression={"{0:N1}" -f($_.FreeSpace/1gb)}}, @{Name='Size (GB)';Expression={"{0:N1}" -f($_.Size/1gb)}} -AutoSize

The value of the parameter ComputerName should be changed to the actual databaseserver, also value in the WQL WHERE clause should be changed to the drive indicated in the error message.

The follow up is to order some additional storage.

I find the error message somewhat misleading. It looks like the ResultSet part is from the connectivity and not from the root error.

History

2011-01-19 : This is postede for the first time.
2013-08-22 : The PowerShell script is added.

1 comment:

Blogging About SQL Tips & Tricks said...

Many thanks for sharing this, it has solved the problem with backing up our database, without having to change the service account being used by SQL server. i have found another helpful post see here: http://sqltechtips.blogspot.in/2015/12/backup-error-15105.html