I have a sql agent job setup for taking full backup every night. Last night the job failed with the following error message.
Sql Server Error Log Message
BackupIoRequest::ReportIoError: write failure on backup device
'H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\My_Database_backup_2014_04_28_233008_2135500.bak'.
Operating system error 2(failed to retrieve text for this error.
Reason: 15100).Error: 3041, Severity: 16, State: 1.
SqlAgent Job History Log
Executed as user: DomainName\sqlagent. Microsoft (R) SQL Server
Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright
(C) Microsoft Corporation 2010. All rights reserved. Started:
23:30:01 Progress: 2014-04-28 23:30:06.81 Source:
{123123123-F70A-430F-8041-12312312313} Executing query "DECLARE @Guid
UNIQUEIDENTIFIER EXECUTE msdb..sp…".: 100% complete End
Progress Error: 2014-04-28 23:30:16.19 Code: 0xC123123
Source: Back Up Database (Full) Execute SQL Task Description:
Executing the query "BACKUP DATABASE [My_Database] TO DISK =
N'H:\MSS…" failed with the following error: "Write on
"H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MY_Database_backup_2014_04_28_233008_2135500.bak"
failed: 2(failed to retrieve text for this error. Reason: 15100)
BACKUP DATABASE is terminating abnormally. 10 percent processed. 20
percent processed. 30 percent processed. 40 percent processed. 50
percent processed. 60 percent processed.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 23:30:01 Finished: 23:30:16 Elapsed: 14.844 seconds. The
package execution failed. The step failed.
After doing the research on web mostly posts suggest I do not have enough space on the disks, I have checked the space on my disks I have have atleast 30 plus GB of free space on almost every drive, (disk for .mdf and disk for .ldf files). I have almost 15 gb of free space on my C drive. as One of the posts on web suggested not having enough space on C drive can also cause issues.
Has someelse ever faced the same issue, how you fixed it? what can be the reason for this error and how I can avoid it in the future. Any suggetions pointers in the right direction are most welcome. Thank you.
Best Answer
Operating system error 2 is a standard Windows operating system error--file not found. Check the permissions on the folder and make sure that the account that owns the agent job has access to the folder and is able to traverse the path to the folder where the backup is trying to write.
Unfortunately, this is a Windows error message and not a SQL error. I found something on Microsoft Connect (related to restore, not backups) where they said they were not able to reproduce the problem and confirmed that this is an OS, not a SQL Server, error message.