SQL Backup takes too much time

[Sisso]

Hello,
I need some advice.

Running Symantec Netbackup 7.5.0.6 on windows server 2008 R2 SP1.
SQL server is 2008 running on server 2008 R2.

Everything works fine, all the databases finish their backups, but unfortunately it takes too long.
We have many instances on the SQL server, and many databases for each instance. Each database is about 4-10 MB.
Their backups take only 10-20 sec. The problem is that untill they start to backup it takes a long time.
I can see the job is Active but writing to disk only starts after 5-10 min.  As a result of this, the instance that has 800 samll databases doesn't finish its whole backup during the night

 Please suggest what should i change to improve the time period until the job actually starts to backup.
This is the script that backups all the databases:
 

OPERATION BACKUP
DATABASE $ALL
EXCLUDE "master"
SQLHOST "bronko"
SQLINSTANCE "instance1"
NBSERVER "MSbackup"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
DUMPOPTION INCREMENTAL
NUMBUFS 2
SQLCOMPRESSION TRUE
RESTARTTYPE AUTO
RESTARTWAITSECONDS 60
NUMRESTARTS 3
BATCHSIZE 20
ENDOPER TRUE
 

[Mark_Solutions]

When the backup runs it has to effectively snapshot each database

Even though they are small you may be pushing the system to do 20 at a time

It is a little like VMware backups where you can often do 200 VM's faster by doing them 1 at a time than you can trying to do 6 at a time

So try changing the BATCHSIZE so see what difference it makes - it is set to 20 at the moment so try 10 and then 5 and then 30 and compare each setting to see the overall difference (if any) - do this until the optimum setting is found

Also bear in mind that you are also compressing 20 at a time - this normally speeds things up but maybe again at 20 it is just too much - so as a second test you could also try the different batch sizes with and without compression

All the other usual stuff also applies in relation to network and data buffers used but during testing only change one thing at a time to see the effect that produces

Hope this helps

[Sisso]

Thank you Mark for suggesting,

I have tried to change the BATCHSIZE value as you suggested until I got the best performance of backup.

I think now that the best way to determine if it useful is to wait some days to see that all the databases finish to backup in the window backup.

[inn_kam]

OPERATION BACKUP

DATABASE "______"

SQLHOST "_____"

NBSERVER "______"

MAXTRANSFERSIZE 6

BLOCKSIZE 7

TRACELEVEL MAX

NUMBUFS 2

RETRYTYPE AUTO

RETRYWAITSECONDS 60

NUMRETRIES 1

ENDOPER TRUE

i am ALSO USING SQL Agent backups via Netbackup 7.5.0.4

please add TRACELEVEL MAX  as shown above in batch file under the block size

and also one must enable dbclient logs on Master server and on SQL agent

then the backup time will be fast

[Sisso · Accepted solution]

eventually, I fix the problem by changing the storage unit from OpenStorage disk device to an advanced disk device.