Competition: What’s your favorite Redgate tool? Enter now.

Insufficient disk space

RussFRussF Posts: 25
edited July 17, 2006 8:33PM in SQL Backup Previous Versions
Hi,

Trying to schdule a backup job for a 60Gig SQL database, destination a network share which has more than enough free space.

Getting a insufficient disk space error.

SQL backup is obviously compressing locally before the copy.

Can I change the path where the compress occurs as C:\ and the drive where the DB is does not have 60Gig free but compressed the DB is only 9Gig and both those drives have more than 10Gig free. I can have another local drive which has 32Gig free and when I backup locally it works, but not when I try to backup to a network share.

Thanks, any info would be appreciated.

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup does not compress locally if you are backing up to a network share.

    A common scenario seems to be that there is some sort of limit on the maximum network transfer. Try running a backup with the MAXTRANSFERSIZE value of 1 MB e.g.

    master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [\\netshare\backups\pubs.sqb] WITH MAXTRANSFERSIZE = 1048576" '

    If that does not work, bring it down to 512 KB (524288), then to 256 KB (262144). You can use any value from 65536 to 4194304 (default), in multiples of 65536.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter,

    The pubs DB which is 1.25meg backs up with no issue via the GUI or using the SQL statement.

    When I try a DB larger such as DB called interface @ 725meg it fails even if I use your statement and use the lowest value:

    master..sqlbackup '-sql "BACKUP DATABASE interface TO DISK = [\\rbsbackup01\SQL Daily\RBSMERSRV01\interface.sqb] WITH MAXTRANSFERSIZE = 65536" '

    See error below:
    Backing up interface (full database) on MERLIN_PROD instance to:
    \\rbsbackup01\SQL Daily\RBSMERSRV01\interface.sqb

    Thread 0 error:
    Error 620: Error writing to backup file(s).
    Process terminated unexpectedly. Error code: -2139684860

    Server: Msg 3013
    BACKUP DATABASE is terminating abnormally.
    Server: Msg 3202
    Write on 'SQLBACKUP_628EEFA3-6247-4294-87F9-49C2D582AF9C' failed, status = 112. See the SQL Server error log for more details.

    SQL Backup exit code: 790
    SQL error code: 3202

    More details
    - I am currently backing up other SQL servers with DB's of around 26gig perfectly to the same share usingn SQLbackup, so permissions is not an issue.
    - I can successfully perform a SQLbackup of any DB on the 'problem' server locally, problem only when doing so to a network share, also tried another network share with the same results.

    Thanks
    Russell
  • peteypetey Posts: 2,358 New member
    I apologise, I made a mistake in my post. It should have been the MAXDATABLOCK parameter, not the MAXTRANSFERSIZE parameter. So pls try something like this:

    master..sqlbackup '-sql "BACKUP DATABASE interface TO DISK = [\\rbsbackup01\SQL Daily\RBSMERSRV01\interface.sqb] WITH MAXDATABLOCK = 1048576" '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter,

    Thanks, managed to get it working on , MAXDATABLOCK = 458752"'

    Is this issue due to there not being enough physical memory to service the request for backup?

    Thanks
    Russell
  • peteypetey Posts: 2,358 New member
    SQL Backup writes out the compressed data in blocks of 4 MB by default. Unfortunately, on some networks, this fails. Hence, the MAXDATABLOCK parameter is used to specify the maximum size of each block that's written. In your case, the maximum sized block that can be written to the network share is 448 Kb.

    I don't know which element on the network (hardware/software/router etc) actually imposes this limit. Any thought on this, anyone?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter,

    Just to add to the ?.

    This only occurs on one of my servers on the LAN, I have other servers backing up perfectly.

    The server is Win2003 SP1 Enterprise with SQL 2000 Enterprise SP3a
    4 way Xeon IBM
    8 Gig physical memory

    We also run SQL replication on that box.

    Thanks
    Russell
Sign In or Register to comment.