Error during Backup for large SQL database

I'm making a regular scheduled daily backup for the database of our ERP system, it was done successfully until the mdb file size exceeded the 20 GB. I'm making the backup on Tape , and also on Disk. There is no problem for Tape, but the Backup on Disk is not successful anymore. The SQL server in on win2k server which has NTFS volumes, the volume on which the Data and Backup resides has 38 GB free space. The mdb size now is 22 GB and the ldb size is 7GB. After the error occur, I find these messages on the following logs :
Job History
-------------
 Write on 'e:\mssql\BACKUP\Everyday Backup' failed,status = 112. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3202)  

Sql Log
--------
BackupDiskFile::OpenMedia: Backup device 'e:\mssql\BACKUP\Everyday Backup' failed to open. Operating system error = 2(The system cannot find the file specified.).

BackupMedium::ReportIoError: write failure on backup device 'e:\mssql\BACKUP\Everyday Backup'. Operating system error 112(There is not enough space on the disk.).

Event Log
----------
The E: disk is at or near capacity.  You may need to delete some files.

What would be the possible reasons for this problem, and what is the solutions for it ?
zizomaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
arbertConnect With a Mentor Commented:
There has to be something else going on during your backup that's causing E: to not have enough space--have you tried perfmon to trace the disk usage during the backups?
0
 
patriktCommented:
Are you adding to existing backup set or writing ower it?

Try to delete/move existing backup set manualy before backup.
0
 
zizomaAuthor Commented:
I always move the last backup to another place on the network. at the same time,I'm setting the option to overwrite the existing media.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
arbertCommented:
Looks pretty clear--did you check he E: for space??
0
 
zizomaAuthor Commented:
If you read my Question carefully you will find this line :
the volume on which the Data and Backup resides has 38 GB free space. which I mean is the E:
0
 
arbertCommented:
"Data and Backup resides has 38 GB free space"

So you're creating your backup on the same drive as the data?  The data is 29gig and you're creating a backup on the drive leaving 9gig free--I'm still betting you're running out of disk space on this drive.  It's not a good idea to backup to the same drive as the data can grow during the backup (the logfile) and cause you to run out of space....

0
 
zizomaAuthor Commented:
The size of latest Backup files created was about only 20GB. so,When the Sql starts the backup procedure it has 38GB free , and after it finish there will be about 18GB remaining. I think this is enough,since I always move the backup file to another Place every day.
0
 
arbertCommented:
The error message would say other wise.  How is the growth setup on your LOG and Data files?
0
 
zizomaAuthor Commented:
Both are automatic by percent 10%
0
 
patriktCommented:
It looks OK. Even if both files will grow during backup it is stil mathematicaly right (22*1,1+7*1,1)=32GB for database. If we try to backup that to 38GB space there is still 6GB free :)

But stop trying to find bug in a big hole. If you can make experiment and backup db to other big disk you'll have good glue to find where is error.
0
 
zizomaAuthor Commented:
I changed the backup destination to another volume which has 32GB free, and it worked fine. what could be the difference? , I hope it's not a problem in the E: volume.
0
 
zizomaAuthor Commented:
That's a good idea, I'll try it. Anyway it seems to be space problem,because I freed more space on the E drive and the backup went fine. But what is minimum space required by the backup? That is the question now.
0
All Courses

From novice to tech pro — start learning today.