July 29, 2009 at 8:16 am
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.
July 29, 2009 at 8:25 am
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.
July 29, 2009 at 8:28 am
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?
July 29, 2009 at 8:42 am
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:
July 29, 2009 at 8:46 am
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.
July 29, 2009 at 9:03 am
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?
July 30, 2009 at 12:55 pm
Is there anything in the Windows event logs around the same time of the backup failure?
July 30, 2009 at 1:00 pm
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.)."
July 30, 2009 at 1:19 pm
Hmmm, Like Steve, I will start reaching here. By any chance is your D:\ drive a FAT32 partition?
July 30, 2009 at 1:29 pm
Thanks for the help Jon. And everything here is NTFS, not FAT32.
July 30, 2009 at 1:33 pm
Does anything else run at 6:00pm that may chew up a bit of space and then release it?
July 31, 2009 at 7:54 am
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
August 1, 2009 at 5:56 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply