SQL Agent GROUPSIZE issue

[rbkguy]

I am having an issue fwith one of my SQL servers running the SQL agent to backup my sql database. Master server is running Netbackup 7.01 on windows 2008 R2 as well as the SQL server with SQL 2005 SP4.

I have attached the log file and one of the scripts I am using as I have over 32 datebases on the server and need to split them up. Instead of creating one job for every 10 databases which I set in the batch file its creates one for each DB. The policy is configured with the snapshot client as well. I have another server that it is working fine on it and the only different is that I am using the $All for database in the batch file because it only has 11 DBs on that server. Any ideas?

[Tim_Hansen · Accepted solution]

The GROUPSIZE parameter must equal the number of databases in the script, with a high limit of 32.

If you wish to use multiple snapshots, create multiple .bch files each with their own GROUPSIZE parameter at the beginning, equalling the number of databases in the script.

Do not use the $ALL variable with the GROUPSIZE parameter.

[rbkguy]

Thanks Tim! Would you say that doing backups this way is faster then doing one queued job for each DB?

[Tim_Hansen]

It depends, if you have numerous tiny databases, then yes I've found the snapshots speed up the process greatly. If you have large databases, then using multiple stripes and larger data transfer sizes works much much better.

My general rule is to use BATCHSIZE for systems with <100 databases, and GROUPSIZE for >100.

[rbkguy]

How about for your transaction logs? Can I use the $ALL DB option with the batchsize option in the script or to I have to create multiply batch files for that as well?

BATCHSIZE 10
OPERATION BACKUP
DATABASE $ALL
EXCLUDE "master"
SQLHOST "*********"
NBSERVER "****-BK-HQ1"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

[Tim_Hansen]

You can use BATCHSIZE with the $ALL parameter, and you cannot use snapshots for txlogs, so using BATCHSIZE is the preferred method for backing up and truncating your transaction logs