cancel
Showing results for 
Search instead for 
Did you mean: 

SQL batch file with BATCHSIZE being ignored

tythecellist
Level 4
Partner

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

1 ACCEPTED SOLUTION

Accepted Solutions

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified
Put BATCHSIZE at the top.
I don't have access to manuals right now, but I seem to remember that it has to be the 1st line. Like in this example:
https://www.veritas.com/support/en_US/article.id-SF930853700_nbu_sql_web

View solution in original post

6 REPLIES 6

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified
Put BATCHSIZE at the top.
I don't have access to manuals right now, but I seem to remember that it has to be the 1st line. Like in this example:
https://www.veritas.com/support/en_US/article.id-SF930853700_nbu_sql_web

Thiago_Ribeiro
Moderator
Moderator
Partner    VIP    Accredited

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

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?

Thiago_Ribeiro
Moderator
Moderator
Partner    VIP    Accredited

Hi @tythecellist,

I dont think that is can be configured on Netbackup side...I think that is SQL behavior, but Im not sure.

Maybe another person, can explain us.

I found this TN https://www.veritas.com/support/en_US/article.000125633, with many values that can be used in batch files, if you want to see.

 

Regards,

 

Thiago

This topic is now resolved. 

a) BATCHSIZE needed to be put as the first line in my DBAs' batch script

b) NBU 7.5.0.6 (client) doesn't talk to 7.7.3 (server) well from a SQL agent perspective for things such as BATCHSIZE. 

c) Upgrading the client (7.5.0.6) to 7.7.2 resolved this issue; BATCHSIZE is now being read and backups run in parallel. 

Thanks all !

-ty

William_Jansen_
Level 5
Partner Certified

Correct me if I'm wrong. But I think if your Max client jobs on Master Server host properties is too low it might also not cause additional jobs to spawn.