SIMPLE Full Backup Fails

Question

SIMPLE Full Backup Fails

rew-370421

Hall of Fame

Points: 3640
More actions
July 29, 2009 at 8:16 am

#224049

Here's my setup:
W2K3 w/sp2
SQL2K5 w/sp3 (v9.0.4035)
Database = TESTDB
size = 18092 MB (space availabe = 9865 MB)
files = 1 MDF, 1 LDF and 7 NDF
recovery model = SIMPLE
Auto Shrink = FALSE
I perform nightly database backups and the Maintenance Plan automatically deletes the OLD Backup Files older than 1 day. The Database Backup Size = 7.54 GB.
The remaining FREE Space on the BACKUP DRIVE is 13.6 GB
Here's my Problem/Question: When backups run the next night, it fails with "There is not enough space on the disk".
If there is 13.6 GB of free space left on the BACKUP DRIVE and a FULL Database Backup is 7.54 GB, why does the job FAIL?
HERE IS THE FULL ERROR MESSAGE
Message
Executed as user: ADMIN. ...sion 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:00:00 PM Error: 2009-07-28 18:04:12.56 Code: 0xC002F210 Source: Back Up Database (Full) Execute SQL Task Description: Executing the query "BACKUP DATABASE [TESTDB] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TESTDB\TESTDB_backup_200907281800.bak' WITH NOFORMAT, NOINIT, NAME = N'TESTDB_backup_20090728180009', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Write on "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TESTDB\TESTDB_backup_200907281800.bak" failed: 112(There is not enough space on the disk.) BACKUP DATABASE is terminating abnormally. 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed.... The package execution fa... The step failed.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

The maintenance plan only does the delete phase on successful completion of the backup phase. That way you don't end up without any backups.

For the duration of the backup, you are therefore going to need enough space for 2 backups... unless you want to manage the deletion of the old backups yourself.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

It's a simple OS error reported to SQL. Either the account has a quota limitation or there truly is not enough space. If you manually run the backup yourself, does it complete? Are other databases being backed up?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 3

Thanks Ian and Steve for your quick replies. 🙂

1) If I manually run the Backup Job it completes fine. If the SQL Job performs the Backup Job, it FAILS.

2) We have NO QUOTAs configured in our shop.

3) There are a couple very tiny databases being backed up and they work fine.

Currently, the Drive has 13.6 GB of FREE SPACE.

I also have 1 Full Backup (the backup file size is 7.54 GB) existing on that drive.

When another backup runs, I should have about 6 GB of Free space still left. Even without the automatic deletion of the previous days backup file.

This doesn't make sense.:crazy:

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

No it definitely sounds strange.

If you can make the test, what if you delete your 7GB backup and then run the job? Does it work?

I'm reaching here, testing different things.

rew-370421 Hall of Fame Points: 3640 More actions · Answer 5

Hi Steve

Yes, I performed these tests, again, today.

1) I deleted ALL Backup Files and there was 21.1 GB of FREE SPACE on the BACKUP DRIVE. I then manually ran the backup job. It ran fine and ate up 7.54 GB.

2) About an hour later, I then manually ran the same backup test again. It also ran fine and it ate up about 7.76 GB of space.

3) The Drive now has 5.87 GB of FREE SPACE left.

Is this a bug where SQL doesn't "know" how much space the O/S drive has?

Or does "SIMPLE" Recovery Mode do something different internally to SQL because the database size itself is about 18 GB?

Jon Russell Hall of Fame Points: 3048 More actions · Answer 6

Is there anything in the Windows event logs around the same time of the backup failure?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 7

Nothing out of the ordinary.

And it says the samething that the SQL Log tells me, "Operating system error 112(There is not enough space on the disk.)."

Jon Russell Hall of Fame Points: 3048 More actions · Answer 8

Hmmm, Like Steve, I will start reaching here. By any chance is your D:\ drive a FAT32 partition?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 9

Thanks for the help Jon. And everything here is NTFS, not FAT32.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 10

Does anything else run at 6:00pm that may chew up a bit of space and then release it?

vince.chevens SSC Enthusiast Points: 197 More actions · Answer 11

Simple – in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.

Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.

this might explain space issue

rew-370421 Hall of Fame Points: 3640 More actions · Answer 12

Ian, you are the man!

I did some testing and determined that our backup system (we use Veritas NetBackup) was the cause. It consumes space temporarily as it backs up "open" files. I've contacted our Veritas Team for them to troubleshoot.

thanks again, Ian