MAXTRANSFERSIZE values

briangabrianga Posts: 4 New member
edited May 3, 2016 9:26PM in SQL Backup Previous Versions
I was just curious if there was going to be changes to the MAXTRANSFERSIZE option?
Mostly wondering because with my testing on our systems, having this at 4MB (4194304), I get better backup and restore times with using standard SQL backup but your software seems to be limited to 1 MB (1048576).
Is there plans to increase this number? 4MB appears to have been supported by SQL Server for quite some time now, and we would like to do pretty much anything that could increase our backup/restore time process.

I am currently testing methods to optimize the backups on our systems based on number of threads, compression level and max transfer size and then doing a space/time comparison to figure out what is the optimal method for each database and process (we have some "one-off" backups that we do where time is more critical than space).

Thanks
Brian
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    The next version of SQL Backup will allow you to specify a BUFFERCOUNT value, which can improve backup throughput in some situations. You can download a patch supporting this option here.

    Try BUFFERCOUNT values of 100, 200, and 300, and see if it makes a difference. E.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASE mydb TO DISK = [g:ackupsmydb_FULL_01a.sqb], DISK = [g:ackupsmydb_FULL_01b.sqb], DISK = [g:ackupsmydb_FULL_01c.sqb], DISK = [g:ackupsmydb_FULL_01d.sqb] WITH BUFFERCOUNT = 200"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.