Solved

Error during Backup for large SQL database

Posted on 2004-08-11
14
325 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now