Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Backup fails due to lack of disk space Expand / Collapse
Author
Message
Posted Saturday, September 26, 2009 4:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:46 PM
Points: 284, Visits: 263
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/
Post #794152
Posted Saturday, September 26, 2009 5:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:05 AM
Points: 201, Visits: 391
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.
Post #794155
Posted Saturday, September 26, 2009 12:58 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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.


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

Post #794191
Posted Monday, September 28, 2009 12:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 2,832, Visits: 8,512
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).



Post #794665
Posted Tuesday, July 27, 2010 3:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:07 AM
Points: 1,806, Visits: 1,331
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.
Post #959733
Posted Tuesday, July 27, 2010 3:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
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
Post #959739
Posted Wednesday, July 28, 2010 5:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 969, Visits: 692
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



    Post #960413
    Posted Thursday, July 29, 2010 11:26 PM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: 2 days ago @ 10:44 PM
    Points: 2,693, Visits: 1,213
    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
    Post #961136
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse