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.

 

10/13/2014 2:00:37 AM - Info dbclient(pid=27536) INF - backup database "B" to VIRTUAL_DEVICE='VN' with  stats = 10, blocksize = 65536, maxtransfersize = 4194304, buffercount = 2
10/13/2014 2:00:38 AM - Info dbclient(pid=27536) INF - Number of stripes: 1, Number of buffers per stripe 2. 
10/13/2014 2:00:38 AM - Info dbclient(pid=27536) INF - Created VDI object for SQL Server instance <SQL>. Connection timeout is <300> seconds.
10/13/2014 6:32:41 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]10 percent processed.>.
10/13/2014 6:37:23 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]20 percent processed.>.
10/13/2014 6:41:50 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]30 percent processed.>.
10/13/2014 6:45:11 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]40 percent processed.>.
10/13/2014 6:48:13 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]50 percent processed.>.
10/13/2014 6:51:25 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]60 percent processed.>.
10/13/2014 6:55:02 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]70 percent processed.>.
10/13/2014 6:58:08 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]80 percent processed.>.
10/13/2014 7:00:55 AM - Info dbclient(pid=27536) INF - ODBC return code <2>, SQL State <01000>, SQL Message <3211><[Microsoft][ODBC SQL Server Driver][SQL Server]90 percent processed.>.
10/13/2014 7:04:19 AM - Info dbclient(pid=27536) INF - Thread has been closed for stripe #0    

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.

     

     

     

     

     

     

6 Replies

Sort By
  • 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

  • Yasuhisa_Ishika's avatar
    Yasuhisa_Ishika
    Level 6
    10 years ago

    Ask SQL admin to check how long native SQL backup to disk takes. If it takes too long, something wrong resides in SQL Server side.

  • H_Sharma's avatar
    H_Sharma
    Level 6
    10 years ago

    Hi Experts,

    What is happening here is backup scheduled at 2 am. Backup policy has 1 client and Sql has 9 instances. Parent runs and start instance 1 and instance 1 is in queue. it gets successfull and parent then start the another child process for instance 2 and its in queue. its happening for all the rest instances.

    These child process for each instance going in different tape drives that why backup is taking time.

    So pls provide the solution.

  • H_Sharma's avatar
    H_Sharma
    Level 6
    10 years ago

    Pls find the script .

     

    OPERATION BACKUP
    DATABASE $ALL
    SQLHOST "SQLCLUSTER"
    NBSERVER " xxxxx"
    MAXTRANSFERSIZE 6
    BLOCKSIZE 7
    BROWSECLIENT "SQLCLUSTER"
    NUMBUFS 2
    ENDOPER TRUE

     

    Pls let me know what to edit in the script file as it has all the 9 instances and backup is going to differnet different tapes and remains in queue as it does not get the drive.

  • Wiriadi_Wangsa's avatar
    Wiriadi_Wangsa
    Level 4
    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.

     

     

     

     

     

     

  • Marianne's avatar
    Marianne
    Level 6
    10 years ago

    In addition to above excellent advice, also confirm that Max Jobs per Client in Host Properties -> Master -> Global Attributes is set to 2 or more. 4 is normally a good figure.

    See:

    Performance tuning for NetBackup for Microsoft SQL Server backups:   http://symantec.com/docs/TECH33423