DB backups taking too long

Dushyant Patel 1 Reputation point
2021-03-01T03:33:04.8+00:00

I've SQL server 2012 and our DB backup average taking 45 minutes to gets done but from last couple of days is taking 5 to 6 hours to get done.
Is there way I can check where It's causing issue from?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,768 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2021-03-01T07:58:33.85+00:00

    Is there way I can check where It's causing issue from?

    Yes when the backup is running look at the wait_type from sys.dm_exec_requests

    select wait_type, wait_resource,* from sys.dm_exec_requests where command like '%backup%'

    In most cases slow backup is due to storage issue because backup reads from storage and writes to storage. I would check disk I/O for drives using dmv sys.dm_io_virtual_file_stats. You can also fire disk related perfmon counters. Specially disk for Avg disk queue length.

    Paul Randal has script created to get I/O stalls on Disk please refer.

    Moral of story is get you disks checked thoroughly.

    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-02T04:07:49.797+00:00

    Hi @Dushyant Patel ,

    Please share us the result of wait_type, wait_resource from sys.dm_exec_requests as shashank mentioned. And check the storage performance, Avg Disk Sec\Write.

    Did you change compression settings for your backups?

    To find out which processes are being run by SQL Server as part of a backup or restore, and how long those processes take, set a trace flag on the SQL Server master database:

    DBCC TRACEON (3004, 3605, -1)

    Setting the trace flag causes additional information to be written to the SQL Server logs. To view the logs, open SQL Server Management Studio and in Object Explorer open Management > SQL Server Logs > Current.

    After test is done, please turn them off this way:

    DBCC TRACEOFF (3004, 3605, -1)

    Please refer to the blogs How to Make SQL Server Backups Go Faster and Is your SQL Server backup running slow? Here’s how you can speed it up to get more.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments