SQL Server

[H_Sharma]

Dear Experts,

I need to know when we create .bch file in SQL server . It has the following 3 options.

MAXTRANSFERSIZE 6

BLOCKSIZE 7

NUMBUFS  2

What are these options and could we change these default values and what are the highest and lowest values for these?

Thanks so very much....

[RamNagalla · Accepted solution]

just a copy from Netbackup admin guide for SQL... 

MAXTRANSFERSIZE

(stream-based only) The size that SQL Server uses for
transferring data. Among the SQL Server-specific factors,
MAXTRANSFERSIZE has the greatest overall potential for
optimizing transfer rates. In general, improved performance
correlates directly with an increased MAXTRANSFERSIZE
value.

BLOCKSIZE 

Setting BLOCKSIZE for NetBackup for SQL Server
The BLOCKSIZE parameter can be set for each backup operation. For restores,
NetBackup for SQL Server automatically chooses the same size that you backed
up with. BLOCKSIZE ranges from 512 bytes to 64 kilobytes. The default is 512
bytes. BLOCKSIZE is the incremental size that SQL Server uses for reading and
writing backup images.
Set the BLOCKSIZE for your NetBackup for SQL Server session using the NetBackup
client properties dialog box. Otherwise, you can use the BLOCKSIZE parameter
to set this value when you create a batch file manually.

NUMBUFS  

Setting NUMBER OF BUFFERS PER STRIPE for NetBackup for
SQL Server
TheNUMBEROFBUFFERSPERSTRIPE parameter affects buffer space availability.
This parameter is set in the NetBackupMSSQL Client interface for each operation.
NetBackup uses this parameter to decide how many buffers to allocate for reading
or writing each data stream during a backup or restore operation. By allocating
a greater number of buffers, you can affect how quickly NetBackup can send data
to the NetBackup master server.
The default value for the NUMBER OF BUFFERS PER STRIPE is 2, which allows
double buffering. You may get slightly better performance by increasing this value
to a higher value.
In the Microsoft SQL Client interface, you can change this value through the
NetBackup client properties dialog box. (File > Set NetBackup client properties).
Otherwise, you can use theNUMBUFSparameter to set this value when you create
a batch file manually.
See “About using batch files”

i would appreciate if you refer the admin guides before asking the theroritical questions..

[RamNagalla]

Wr .. beat me..

[Will_Restore · Accepted solution]

from page 52 in the Symantec NetBackup™ for Microsoft SQL Server Administrator's Guide

MAXTRANSFERSIZE
The MAXTRANSFERSIZE parameter can be set for each backup or restore
operation. It ranges in size from 64 kilobytes to 4 MB. The default is 64 kilobytes.
MAXTRANSFERSIZE is the buffer size used by SQL Server for reading and writing
backup images. Generally, you can get better SQL Server performance by using a
larger value.
Set the MAXTRANSFERSIZE for your NetBackup for SQL Server session by
choosing File > Set NetBackup client properties or add this parameter to batch
file.
See “Using batch files” on page 105.

BLOCKSIZE
The BLOCKSIZE parameter can be set for each backup operation. For restores,
NetBackup for SQL Server automatically chooses the same size that you backed
up with. BLOCKSIZE ranges from 512 bytes to 64 kilobytes. The default is 512
bytes. BLOCKSIZE is the incremental size that SQL Server uses for reading and
writing backup images.
Set the BLOCKSIZE for your NetBackup for SQL Server session using the NetBackup
client properties dialog box. Otherwise, you can use the BLOCKSIZE parameter
to set this value when you create a batch file manually.
See “Using batch files” on page 105.

NUMBER OF BUFFERS PER STRIPE
TheNUMBEROFBUFFERSPERSTRIPE parameter affects buffer space availability.
This parameter is set in the NetBackupMSSQL Client interface for each operation.
NetBackup uses this parameter to decide how many buffers to allocate for reading
or writing each data stream during a backup or restore operation. By allocating
a greater number of buffers, you can affect how quickly NetBackup can send data
to the NetBackup master server.
The default value for the NUMBER OF BUFFERS PER STRIPE is 2, which allows
double buffering. You may get slightly better performance by increasing this value
to a higher value.
In the Microsoft SQL Client interface, you can change this value through the
NetBackup client properties dialog box. (File > Set NetBackup client properties).
Otherwise, you can use theNUMBUFSparameter to set this value when you create
a batch file manually.
See “Using batch files” on page 105.