Database Backup fails due to lack of disk space

Question

Database Backup fails due to lack of disk space

sivaprasad

SSCertifiable

Points: 6183
More actions
September 26, 2009 at 4:55 am

#385443

I have a database sizing 55 GB. The database backup job created on SQL Server 2005 Maintenance Plan.
The total disk space on the drive is 100 GB . The retention period for the database backup is 22 Hours.
The full database backup job scheduled by 10:00 PM daily, based on the client requirement.
Since the backup job created using SQL Server 2005 Maintenance plan, it require double the disk space to have successfull backup.
Because, as per design the yesterday backup (55 GB ) will be deleted only on successful of todays backup.
We advised the client to delete the yesterday database backup before database backup job kick on today. This client rejected as on any eventuality happening during the backup job window, we will not be able to recover the database.
Client advising to have a look on the following options,
1. Use pZip, 7-Zip, WinZip to compress the database backup after SQL Server database backup job completion.
2. Strip the database backup with multiple backup devices on the same disk volume. Enable the NTFS compression on the backup device folder.
I would welcome any workaround ,good solution on handling this issue.
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]
Upgrade to SQL Server 2008 which has native backup compression. The caveat being that it only works if TDE is not enabled.
Tell the client to back up the directory onto tape. This way you can delete the old backup prior to creating a new one.

Third party tools or another drive would be the much cheaper alternative even if it's just a cheap usb device that you can transfer the backup onto directly after it has been created. Ideally if you are copying the data it should be onto another server that is being backed up to tape.

Hope this helps,
Rich

[p]
[/p]

Sergey Vavinskiy SSCarpal Tunnel Points: 4469 More actions · Answer 1

The first option is reasonable, except WinZip doesn't work with the files of such size.

The second option is not clear, I don't see how stripped backup can help you with disk space issue.

You may also want to use third-party software (RedGate, LiteSpeed, etc.) to resolve this problem.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

Is the backup copied off the server to another location after completion (say to tape)?

If so its safe to overwrite the backup file on disk each night, Use a job executing a backup command with init rather than a maintenance plan.

If the db is in full mode and you are doing log backups you can also recover the database up to the minute using those.

Hyperbac is another tool which will backup the database to a compress ed file. easy and quick to install.

---------------------------------------------------------------------

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Sergey's suggestion of a 3rd party tool is good. They compress to about 20% of original size (depending on your data) and are faster than native SQL backup. Then you can fit 4 backups on your drive.

Or buy another or bigger drive. Shouldn't cost much for the size you're working with.

Also, as George brought up, copying off to another location not only frees up the space, but provides protection by having your backup in another location, especially if your database is on the same drive (bad).

Meet George Jetson SSChampion Points: 11626 More actions · Answer 4

Is this a full backup being taken? If so, try running a transaction log backup five minutes prior, this will shrink the log file, which will make the full backup smaller.

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

homebrew01 (9/28/2009)
Also, as George brought up, copying off to another location not only frees up the space, but provides protection by having your backup in another location, especially if your database is on the same drive (bad).

I would use this approach. I think i would also invest the money to get another drive added (either internal or external). Storage is cheap these days and well worth the money.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 6

chris.s.powell (7/27/2010)
Is this a full backup being taken? If so, try running a transaction log backup five minutes prior, this will shrink the log file, which will make the full backup smaller.

This isn't true. backing up a log file does not shrink it, it merely flushes out all transactions that have been marked as completed.

There are other options which have not been mentioned.

philcart SSC-Forever Points: 47794 More actions · Answer 7

Best option would be to upgrade to SQL 2008 R2 and take advantage of the backup compression. Failing that, grab hold of a third-party compression tool. There are quite a few of them around now, and they're all pretty reliable.

Get your client to shell out for more disk space, allocate space from SAN/NAS, these are only backups, so performance isn't that critical.

Final option would be to get a couple of USB drives (probably <$200). Backup the database to local disks then move it to the USB drive. Maintain 4-5 days of backups on the USB drive and swap the drives around every couple of days.

--------------------
Colt 45 - the original point and click interface