1

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.

Improve this question
7
  • What if you try to take a backup now yourself, does it fail with the same? How big is the database?
    – Allan S. Hansen
    Commented Apr 29, 2014 at 9:48
  • I took a backup this morning without any issues. Database is just under 15BG.
    – M.Ali
    Commented Apr 29, 2014 at 9:49
  • Is it possible the Agents scheduled runtime conflict with other running jobs on the server? Can the Agent also backup now?
    – Allan S. Hansen
    Commented Apr 29, 2014 at 9:50
  • There is no job running at that time. there is atleast 15 minutes time gap between this job and any other job.
    – M.Ali
    Commented Apr 29, 2014 at 9:52
  • Are you by any chance backing up to a compressed disk? If so, the disk can 'fill up' before all the estimated space is used.
    – RLF
    Commented Apr 29, 2014 at 12:11

1 Answer 1

Reset to default
5

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.

Improve this answer
3
  • Thank you for your answer, but sql server agent account does have all the permission to access the drive. The job has been executing without any issues, its only happened a few times in past few months, I just wanted to find out the reason of this random and possibly fix it.
    – M.Ali
    Commented Apr 29, 2014 at 19:12
  • Unfortunately, this is a Windows error message and not a SQL error message. I'll update my answer.
    – Katherine Villyard
    Commented Apr 29, 2014 at 19:13
  • 1
    Thank you very much for help Katherine, The mystery has been solved :). I did do a lot of googling at work but did not find anything on internet except some folks suggesting this can happen if you do not have enough space on C drive, which didnt make much sense. But your answer does help to understand the issue a bit more. finally my job is done here and I can pass this on to System Administrator to look into it :)
    – M.Ali
    Commented Apr 29, 2014 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.