Forum Discussion

Sisso's avatar
Sisso
Level 5
11 years ago

SQL Backup takes too much time

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
 

Agent for Microsoft SQL Server
Backup and Recovery
NetBackup
Windows Server (2003-2008)
  • Sisso's avatar
    Sisso
    11 years ago

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

4 Replies

Sort By
  • Mark_Solutions's avatar
    Mark_Solutions
    Level 6
    11 years ago

    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's avatar
    Sisso
    Level 5
    11 years ago

    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's avatar
    inn_kam
    Level 6
    11 years ago
    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's avatar
    Sisso
    Level 5
    11 years ago

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