Forum Discussion

tythecellist's avatar
tythecellist
Level 4
8 years ago

SQL batch file with BATCHSIZE being ignored

I have a customer running a SQL environment on Windows 2008 with 7.5.0.6 client (unsupported, yes) into a 7.7.3 master server which sends backups on their way any available media server and onto a DataDomain (disk) appliance. 

A batchfile on the Windows host is configured as follows:

OPERATION BACKUP
DATABASE $ALL
EXCLUDE Conversion_XX
EXCLUDE Conversion_YY
EXCLUDE Conversion_ZZ
EXCLUDE dpa_repository
EXCLUDE DBA
EXCLUDE master
EXCLUDE msdb
EXCLUDE tempdb
SQLHOST "CLUSTERNAME"
BROWSECLIENT "NODENAME2"
NBSERVER "NBUMASTER"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
BATCHSIZE 20
STRIPES 1
POLICY PTC_DB_SQL_BACKUP003
NBSCHED Daily
OBJECTTYPE TRXLOG
NUMBUFS 1
ENDOPER TRUE

... and is being called from within SQL as expected.   Backups complete successfully and are recoverable. So, that's good.

Here's what's weird:  the databases continue to peel off the SQL host one at a time, even though BATCHSIZE is set to 20.

I've advised our customer to request an upgrade of that 7.5.0.6 client to 7.7.2, but I've meanwhile searched through this forum as well as Veritas' KB articles without finding anything that indicates this to be a bug.

Has anyone else experienced this ?

Thanks!

ty

7.5
SQL
Windows Server 2008 R2
  • Thiago_Ribeiro's avatar
    Thiago_Ribeiro
    Moderator
    8 years ago

    Hi,

    I agree. I used BATCHSIZE option in some policies in my environment, and I used at top of backup scripts, just as Marianne, said...I even used this same technote.

    Performance tuning for NetBackup for Microsoft SQL Server backups - https://www.veritas.com/support/en_US/article.TECH33423

    BATCHSIZE is the number of databases NetBackup will attempt to back up simultaneously. If, for example, there are 50 databases on the SQL server, and the BATCHSIZE variable is set to 5, when the backup initiates, the NetBackup SQL extension will grab the first five databases, and start running the backup of those five databases. Depending on the configuration of the media server and the storage units, the backup will use five drives. See the technote "How to back up multiple Microsoft SQL Databases in parallel using more than 1 tape drive" in the Related Documents section below for more information on how to use multiple drives for this type of operation.In general, if SQL server has only a few large databases, use of stripes will improve performance. If the server has numerous smaller databases, Batchsize will improve performance. Please note it is possible to use both stripes and batchsize at the same time, however, be careful not to overwhelm the system resources.  

     

    Regards,

     

    Thiago

    • tythecellist's avatar
      tythecellist
      Level 4
      8 years ago

      Thank you both for your replies; I've changed the .bch file.

      Analyzing the parent job's detailed logs, I'm seeing something I can't explain:  a 10-second delay between databases' backups being initiated from SQL:

      08/24/2017 17:32:04 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 165
      08/24/2017 17:32:14 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 166
      08/24/2017 17:32:24 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 167
      08/24/2017 17:32:34 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 168
      08/24/2017 17:32:44 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 169
      08/24/2017 17:32:54 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 170
      08/24/2017 17:33:04 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 171
      08/24/2017 17:33:14 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 172
      08/24/2017 17:33:24 - Info dbclient (pid=3564) Batch = BACKUP003_WKCCHPRDSQLP1V_TRLOGL_DAILY.bch, Op# = 173

      Is this something built-in to MSSQL or specified in NBU somewhere?