DB Backups taking more time

  • Hi All,

    We have configured 2 jobs for db full backups and both jobs having the same number of databases

    [ i.e JOB 1 - Total size of all dbs - 1.07 TB and i.e JOB 2 - Total size of all dbs - 1.11 TB].

    But JOB 1 is taking more than 30 hours for full backup of all databases and

    JOB 2 is taking more than 9 hours for full backup of all databases.

    Can some one please advice on this.

    Thanks and Regards,

    Ravi.

  • Databases on the same server? Do you use backup compression? Size of backups?

  • Disregarding the size of the databases, how many user connections does each have? What are the transaction rates? If there is more activity on one than the other, then you're more likely to be dealing with contention on one which can lead to the longer backup time.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ravisamigo (7/16/2012)


    Hi All,

    We have configured 2 jobs for db full backups and both jobs having the same number of databases

    [ i.e JOB 1 - Total size of all dbs - 1.07 TB and i.e JOB 2 - Total size of all dbs - 1.11 TB].

    But JOB 1 is taking more than 30 hours for full backup of all databases and

    JOB 2 is taking more than 9 hours for full backup of all databases.

    Can some one please advice on this.

    Thanks and Regards,

    Ravi.

    Are you using a 3rd party backup product such as Litespeed or SQLBackup> If so and you have recently ramped up the compression levels this will cause backups to run significantly slower.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    If the size of databases are very large and you are using backup compression technique then you should not include the Verify Backup option in the Backup job as the Verify process takes the hell lot of time to check whether the database is fit to be restored or not. You can create a seperate job to verify the backups using the stored procedure to Verify Backups.

    Let me know if you have any questions.

    Thanks

    Devesh

  • The size of the DB from sp_helpdb is quite different from the actual size i..e the size being used which would be the main consideration for backup's.

    - Please compare the free space and the space used for the DB mdf & ndf files

    - I would also check the NTFS cluster size on the backup disk's for such a huge difference

    - Are both backing-up to SAN, I am considering yes from the size: Please check the SAN metrics

    - Do both of them have the same hardware i..e CPU,RAM, SAN, Physical Vs Virtual server etc ?

    ...Kumar

  • Devesh_Srivastava (7/19/2012)


    Hi,

    If the size of databases are very large and you are using backup compression technique then you should not include the Verify Backup option in the Backup job as the Verify process takes the hell lot of time to check whether the database is fit to be restored or not. You can create a seperate job to verify the backups using the stored procedure to Verify Backups.

    Let me know if you have any questions.

    Thanks

    Devesh

    Not so sure about that. Verify is a pretty soft process. Maybe you're thinking about CHECKSUM or CHECKSUM plus verify. Those can cause backups to slow down.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ravisamigo (7/16/2012)


    Hi All,

    We have configured 2 jobs for db full backups and both jobs having the same number of databases

    [ i.e JOB 1 - Total size of all dbs - 1.07 TB and i.e JOB 2 - Total size of all dbs - 1.11 TB].

    But JOB 1 is taking more than 30 hours for full backup of all databases and

    JOB 2 is taking more than 9 hours for full backup of all databases.

    Can some one please advice on this.

    Thanks and Regards,

    Ravi.

    Have you noticed any jobs wich are running on teh JOB1 databases at same time while they are backing up.

    They might slow down the backups.

    also , is the server same on whcih both jobs are running...

    Regard,s

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • Are they going to the same I/O system?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply