Insufficient disk space

RussF · July 15, 2006 2:47AM

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.

petey · July 15, 2006 5:14AM

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.

RussF · July 17, 2006 5:28AM

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

petey · July 17, 2006 6:43AM

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" '

RussF · July 17, 2006 7:49AM

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

petey · July 17, 2006 12:49PM

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?

RussF · July 17, 2006 8:33PM

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

Insufficient disk space

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk