?
Solved

Error during Backup for large SQL database

Posted on 2004-08-11
14
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 1000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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