none
Full backup took longer time than usual RRS feed

  • Question

  • Hi All,

    We have a sql backup job which usually takes less than 10 min to complete. But today it has taken more than 2 hours to complete. How to find the root cause & how to fix it.


    Tuesday, November 12, 2019 3:56 PM

Answers

  • Backup is basically only I/O. Read the extend from the database file and write to the backup device. So, there's not much (anything) inside SQL server than can affect this. In other words, you most likely had an issue with your storage (SAN?) at that point in time. 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Tuesday, November 12, 2019 7:59 PM
    Moderator
  • All I can think of the drives on which you were trying to take backup was overwhelmed with I/O requests, this can be confirmed from errorlog. Do you see message like "I/O request taking longer than 15 sec" ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Wednesday, November 13, 2019 7:21 AM
    Moderator
  • Hi SSG92,

     

    It seems that there is a serious problem on your disk, as Shanky said, please check your error log for 'I/O request taking longer than 15 sec'. Is there any other process that occupies system resources when you make a backup? We recommend doing backups in the early morning to reduce resource contention.Is your backup file storage location on the same disk as the data file? I recommend that you store your backup files in a different location than the data files, reducing disk pressure.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Wednesday, November 13, 2019 8:54 AM

All replies

  • Check your machine available resource, HIGH CPU or IO disk, and so on.
    Tuesday, November 12, 2019 4:59 PM
  • Everything seems to be fine now.
    Tuesday, November 12, 2019 6:01 PM
  • On checking saw that for a particular DB (DB1) it has take around 1hr 55 min to complete the backup.

    Tried taking manual backup for the same database & it completed in less than 2min now. The backup size remains the same. Have posted the backup history for last 5 days. Please check the time taken. 

    What could have caused the job to take more time to backup this DB today. Can any other process trying to perform any select\insert\update operation wile backup job was running can contribute to this issue?

    database_name physical_device_name bkSize TimeTaken backup_start_date
    DB1 F:\SQLBackups\DB1_nov12_adhoc.bak 51813 MB 162 Seconds 18:21.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_12_030504_4692450.bak 51755 MB 7100 Seconds 15:54.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_11_030501_7291491.bak 51647 MB 168 Seconds 05:09.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_10_034500_8219912.bak 51895 MB 176 Seconds 45:08.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_09_030505_7204550.bak 52115 MB 959 Seconds 05:58.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_08_030504_8357301.bak 52005 MB 147 Seconds 05:11.0
    DB1 F:\SQLBackups\Full\DB1\DB1_backup_2019_11_07_030505_1948589.bak 51758 MB 157 Seconds 05:12.0

    Tuesday, November 12, 2019 7:36 PM
  • Backup is basically only I/O. Read the extend from the database file and write to the backup device. So, there's not much (anything) inside SQL server than can affect this. In other words, you most likely had an issue with your storage (SAN?) at that point in time. 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Tuesday, November 12, 2019 7:59 PM
    Moderator
  • All I can think of the drives on which you were trying to take backup was overwhelmed with I/O requests, this can be confirmed from errorlog. Do you see message like "I/O request taking longer than 15 sec" ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Wednesday, November 13, 2019 7:21 AM
    Moderator
  • Hi SSG92,

     

    It seems that there is a serious problem on your disk, as Shanky said, please check your error log for 'I/O request taking longer than 15 sec'. Is there any other process that occupies system resources when you make a backup? We recommend doing backups in the early morning to reduce resource contention.Is your backup file storage location on the same disk as the data file? I recommend that you store your backup files in a different location than the data files, reducing disk pressure.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SSG92 Friday, December 13, 2019 9:23 AM
    Wednesday, November 13, 2019 8:54 AM
  • Hi All,

    Thank you for the answers.

    Yes. The server has disk IO issue. There were few msgs like  'I/O request taking longer than 15 sec' on the server.

    Backups are being taken on a dedicated drive. The servers are quite busy all the time & some or the other process keeps running. 

    Currently the backups are happening within time as expected.{ I was trying to monitor the server for continuous 3 days when the backup job runs.. But unfortunately, the jobs completed quickly.  So didn't get a chance to know what was the exact case here.

    Friday, December 13, 2019 9:23 AM