What are the challenges you face when working across database platforms? Take the survey
Options

Using FILECOUNT and resources

ChrisAVWoodChrisAVWood Posts: 361
edited April 27, 2011 11:47AM in SQL Backup Previous Versions
Hi,

I know that SQLBackup uses the MemToLeave memory especially in SQL2005 32-bit. We have had to adjust the -g parameter to make sure we can run many backup jobs at a time. We are starting to get some large backups so I am interested in using the FILECOUNT = parameter to produce multiple backup files. I would like to know if this will use more MemToLeave memory and will it speed up backup and restore operations? Is there a good suggestion to what to set FILECOUNT to? In one case we have a single backup getting close to 20Gb so I want to bring this down and wondered whether FILECOUNT = 2 or maybe even FILECOUNT = 4 would be good. I have tested backing up to 2 files and then restoring from those 2 so I have no problem creating scripts/jobs to create multi backup runs.

Thanks

Chris
English DBA living in CANADA

Comments

  • Options
    peteypetey Posts: 2,358 New member
    You should first determine if your existing backup throughput with one file is constrained by your backup read or write process. This link provides some suggestions on how to measure that.

    Also, FILECOUNT does not automatically spread the files across physically different drives. It only creates the new files on the same drive as the primary backup file.

    Lastly, using multiple files do increase the need for more virtual address space. The calculation is as follows:

    13 - 32 threads/files: 2 x threads/files x a
    9 - 12 threads/files: 3 x threads/files x a
    7 - 11 threads/files: 4 x threads/files x a
    6 threads/files: 5 x threads/files x a
    1 - 5 threads/files: 6 x threads/files x a

    where a represents the MAXTRANSFERSIZE value, which is 1 MB by default.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks for the info Petey.

    So going from 1 output to say 3 outputs would take the virtual address space requirement from 6Mb to 18Mb.

    I am just looking at reducing the size of the backup file because of possible limitations in W2K3 R2 for copying files. I should be able to copy 3 7Gb backups rather than try 1 21Gb backup.

    Thanks

    Chris
    English DBA living in CANADA
  • Options
    peteypetey Posts: 2,358 New member
    Another option may be to use a smaller MAXTRANSFERSIZE value. The usual effect of this may be reduced throughput and/or compressibility, but you would need to test it out yourself on your backups to determine if the above applies to you.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks Petey. Reducing the MAXTRANSFERSIZE would bring the VAS down when we add the FILECOUNT parameter.

    Chris
    English DBA living in CANADA
Sign In or Register to comment.