backup error. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

backup error.

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-08 : 15:37:01

18210 :BackupMedium::ReportIoError: write failure on backup device 'E:\Back_Maint'. Operating system error 112(There is not enough space on the disk.).

E: drive is 40gb

sp_helpdb maintsys
size: 132.94 MB

anyone know why is that error happening? my e: drive is 40gb.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-08 : 15:49:05

How much free space on e:\? 40gb?

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-08 : 22:21:10

rmiao,
yes 40gb

i think the file doesnt let me to do any more backup cause it reach the limit or sommething...can a file be reach a limit? or this file is corrupted?

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 08:47:55

Sounds to me like you are out of disk space.

What's the total of MDF and LDF files? If that is more than the available space on E: that may be your problem (ordinarily the backup file will not include any unused space, but it may be including some/lots of stuff from the LDF, as well as the "used" part of the MDF file. Particularly if the database is in FULL Recovery Model and you have not, or "not recently", backed up the Log file.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 10:49:52

What kind of file system? NTFS or FAT32?

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-09 : 15:23:46

rmiao. its FAt32
any difference?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 15:39:33

Post the results of this:

EXEC master.dbo.xp_fixeddrives

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 15:42:52

There is file size limit of 4gb in fat32.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-09 : 16:45:15

tkizer,
C 105846
E 47851

rmiao,
interesting. thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 16:55:35

According to xp_fixeddrives, you've got almost 47GB free on E.

Could you post the command that you are using that gives this error? Also, what is the size of the MDF and LDF for this database?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 17:41:32

That doesn't make sense to me. You said your database is only 132MB, so why would it need to create a backup file that reached the limit of FAT32?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-09 : 20:26:33

tkizer,
BACKUP DATABASE [MaintSys] TO DISK = N'E:\Back_Maint' WITH NOINIT , NOUNLOAD , NAME = N'MaintSys backup', NOSKIP , STATS = 10, NOFORMAT

mdf 42mb
ldf 90mb

yes DB is 132mb
sp_helpdb maintsys
size: 132.94 MB

i didnt create the BAckup.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 22:11:43

The backup file should be around 42MB.

I'm not sure why you are encountering this problem. I can only speculate.

Does it work if you backup to the C drive? How about if you use E:\SomeFolder\...?

What does SELECT @@VERSION show?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-10 : 08:26:37

u know when i use the sme command to backup with another name like
BACKUP DATABASE [MaintSys] TO DISK = N'E:\heyYOU' WITH NOINIT , NOUNLOAD , NAME = N'MaintSys backup', NOSKIP , STATS = 10, NOFORMAT

the file heyYOU is only 4 MB.

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 09:51:01

NOINIT is appending backups to the file. Eventually the file will be too big.

How big is the original backup file you mentioned:

E:\Back_Maint

??

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-10 : 12:02:27

Good catch Kristen.

Use WITH INIT instead to create new files.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-05-10 : 13:14:57

I seem to be seeing a lot of backups-to-disk using NOUNLOAD NOSKIP NOFORMAT etc. just recently. Dunno why though!

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-10 : 16:23:03

tkizer,

noinit means no overwrite.
i dunno if thats what i want.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-10 : 16:26:11

I know what noinit means.

I'm a Sr. DBA!

We can't tell you what you want. You have to decide that on your own.

We use init for all of our backups. We timestamp the files.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-05-11 : 04:28:35

"i dunno if thats what i want"

You have a 40MB database. Your backups are probably about 40MB. You have been appending ALL your backups to a single file. That file is now 4GB big. So that file now contains over 1,000 backups ... is that what you want? Somehow I doubt it!

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-11 : 08:51:56

kristen,
if i use "init" will i lose previous backups?
cause init is overwrite.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources