Using FILECOUNT and resources

ChrisAVWood · April 21, 2011 1:07PM

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

petey · April 21, 2011 10:18PM

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.

ChrisAVWood · April 26, 2011 1:24PM

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

petey · April 26, 2011 10:46PM

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.

ChrisAVWood · April 27, 2011 11:47AM

Thanks Petey. Reducing the MAXTRANSFERSIZE would bring the VAS down when we add the FILECOUNT parameter.

Chris

Using FILECOUNT and resources

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk