Solved

Error during Backup for large SQL database

Posted on 2004-08-11
14
328 Views
Last Modified: 2009-07-29
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 ?
0
Comment
Question by:zizoma
  • 6
  • 4
  • 2
14 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 11772060
Are you adding to existing backup set or writing ower it?

Try to delete/move existing backup set manualy before backup.
0
 

Author Comment

by:zizoma
ID: 11772357
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
 
LVL 34

Expert Comment

by:arbert
ID: 11772868
Looks pretty clear--did you check he E: for space??
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:zizoma
ID: 11774324
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
 
LVL 34

Expert Comment

by:arbert
ID: 11774403
"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
 

Author Comment

by:zizoma
ID: 11774729
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
 
LVL 34

Expert Comment

by:arbert
ID: 11774794
The error message would say other wise.  How is the growth setup on your LOG and Data files?
0
 

Author Comment

by:zizoma
ID: 11775157
Both are automatic by percent 10%
0
 
LVL 12

Expert Comment

by:patrikt
ID: 11781033
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
 

Author Comment

by:zizoma
ID: 11781120
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
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 11786641
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
 

Author Comment

by:zizoma
ID: 11798869
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question