Forum Discussion

rbkguy's avatar
rbkguy
Level 4
14 years ago
Solved

SQL Agent GROUPSIZE issue

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?

7.1.x and Earlier
Agent for Microsoft SQL Server
Agents
Backup and Recovery
NetBackup
Windows
  • Tim_Hansen's avatar
    Tim_Hansen
    14 years ago

    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.

5 Replies

Replies have been turned off for this discussion
  • Tim_Hansen's avatar
    Tim_Hansen
    Level 5
    14 years ago

    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's avatar
    rbkguy
    Level 4
    14 years ago

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

  • Tim_Hansen's avatar
    Tim_Hansen
    Level 5
    14 years ago

    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's avatar
    rbkguy
    Level 4
    14 years ago

    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's avatar
    Tim_Hansen
    Level 5
    14 years ago

    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