Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Backup fails due to lack of disk space


Database Backup fails due to lack of disk space

Author
Message
sivaprasad
sivaprasad
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 269
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 ]http://sivasql.blogspot.com/
Sergey Vavinskiy
Sergey Vavinskiy
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 392
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
george sibbald
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6998 Visits: 13687
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
homebrew01
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3247 Visits: 9083
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
Meet George Jetson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2713 Visits: 1393
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
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22617 Visits: 18259
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

RichardDouglas
RichardDouglas
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1001 Visits: 707
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.
  • 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



    philcart
    philcart
    SSCrazy
    SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

    Group: General Forum Members
    Points: 2946 Visits: 1435
    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.

    Hope this helps
    Phill Carter
    --------------------
    Colt 45 - the original point and click interface

    Australian SQL Server User Groups - My profile
    Phills Philosophies
    Murrumbeena Cricket Club
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search