Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What if you try to take a backup now yourself, does it fail with the same? How big is the database? –  Allan S. Hansen Apr 29 at 9:48
    
I took a backup this morning without any issues. Database is just under 15BG. –  M.Ali Apr 29 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 Apr 29 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 Apr 29 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 Apr 29 at 12:11

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Apr 29 at 19:12
    
Unfortunately, this is a Windows error message and not a SQL error message. I'll update my answer. –  Katherine Villyard Apr 29 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 Apr 29 at 19:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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