Forum Discussion

H_Sharma's avatar
H_Sharma
Level 6
10 years ago

Sql backups are taking time.

Hello Experts. Our sql backup policy is taking more than 13 hours to get completed. Pls find the extracted log on 13 after 2 AM it has another log of 6:32 Am. What might be the issue.   1...
7.6
Backup and Recovery
Basics
NetBackup
Troubleshooting
Windows Server (2003-2008)
  • Wiriadi_Wangsa's avatar
    Wiriadi_Wangsa
    10 years ago

    Hi Hargyan,

    If waiting for tape is the issue, a combination of BATCHSIZE and multiplexing may help. 

    For example, if you set BATCHSIZE to 3, and enable set media multiplexing to 3, you may be able to stream 3 DB backups to one tape at the same time.

    NOTE: Doing this will speed the backup, however restore may be slower as it takes more time to extract data from a multiplexed tape. I just want to make sure you make an informed decision.

    If you do go ahead, just add this line (the one in bold) just above ENDOPER TRUE:

    BATCHSIZE 3

    Then go to the Admin Console > Policies > double click the SQL backup policy > go to Schedules > double click the Default Application Backup schedule > change the Media multiplexing to 3.

    Next, go to the Storage > Storage Units > double click the tape storage unit you use for SQL backup, tick "Enable Multiplexing" and set Maximum streams per drive to 3.

    Click OK.

    I use 3 in this example, but the max number of multiplexing can be up to 32. If you set to high, make sure your SQL Server, media server & tape can handle the high number.

     

     

     

     

     

     

Wiriadi_Wangsa's avatar
Wiriadi_Wangsa
Level 4
10 years ago

Hi Hargyan,

Unfortunately the above snippet doesn't tell much other than the backup has been running for a couple of hours.

You can try this and see if speeds up the data transfer:

From NBU Admin Console, go to Host Properties > Clients > double click the SQL Server > Properties > Windows Client > Client Settings > set Communication buffer size to 1024 kilobytes.

If you have large databases, using STRIPES in the batch file may help. It splits the backup job for a database into the number of streams you specify.

If you have many small databases, using BATCHSIZE may help. If you set to 5, for example NBU will grab 5 databases in one operation. 

As with other backups, the time taken to complete your SQL backup depends on many factors like the number of DBs x their size, hardware (CPU, memory, disk & network) spec &/ performance etc. You might want to do performance tests against various clients, data type (e.g. backing up just normal files) and confirm where the bottleneck is.

Cheers,

Adi