4

We have an SQL Server 2014 Enterprise Edition database around 3 TB in size. We are taking compressed full backup weekly, it was working fine till last week and now suddenly it is taking 18 hours to complete.

When backup completes the backup size is around 550 GB.

Backup drive has around 950 GB disk space free.

What could be the issue?

Improve this question
8
  • 1
    Is it possible the service account was changed, or removed from administrators group, or group policy changed to no longer support IFI? See here for some other ideas on gathering your own diagnostics.
    – Aaron Bertrand
    Feb 16, 2017 at 16:22
  • None of this has been changed
    – AzimL
    Feb 16, 2017 at 16:38
  • 4
    How long was the full backup taking before? Did it go from 12hrs to 18hrs? Or from 1hr to 18hrs?
    – BradC
    Feb 16, 2017 at 21:38
  • Is the backup drive a local drive, or on shared network storage?
    – T.H.
    Feb 17, 2017 at 16:01
  • 1
    Back up go from 12hr to 18hr
    – AzimL
    Feb 17, 2017 at 22:39

1 Answer 1

Reset to default
12

There's basically 4 things involved with backup speed:

  1. Reading from the data files - you can test this by doing a backup to NUL:, as in this example, which won't break your log-backup chain for full recovery model databases:

    BACKUP DATABASE xyz TO DISK = 'NUL:' WITH COPY_ONLY;

  2. Compressing the data - watch CPU pressure during the backup.

  3. Getting it through the network path to its final location (optional, only for network backups) - test this with a network file copy.
  4. Writing it to the disk on its final target location - test this with something like CrystalDiskMark or DiskSpd, but has to be done locally (not over a network pipe, since you want to avoid #3 affecting your results).

Third party backup products like Quest LiteSpeed, Idera SQLsafe, and Red Gate SQL Backup (disclaimer: I'm independent, but I've worked with all of those companies in the past) have wizards that will test each component of that, and then tell you where the bottleneck is. If you want to find it yourself, you're going to have to test each of those components, and then find the weakest link in the chain (the lowest throughput).

For example, if other SQL Servers are writing to the same target location, they may have suddenly had an increase of data or added more servers, thereby slowing down the target.

Improve this answer
6
  • there's also some fun XML shredding to be done with the databases_backup_restore_throughput XE. at one point i'd even figured out how to isolate events by backup stripe. it turned my brain to goo, though.
    – swasheck
    Feb 16, 2017 at 19:46
  • Can't there also be locking issues that would cause the backups to pause while waiting for a lock to release?
    – CaM
    Feb 16, 2017 at 21:16
  • Not a lock inside the database, but there can be locks on the msdb backup history tables (although that's fairly unusual.)
    – Brent Ozar
    Feb 16, 2017 at 21:43
  • @BrentOzar - I hope you don't mind my edit. Also, I'm starting to think you're trolling me with the disclaimers ;-)
    – Hannah Vernon ♦
    Feb 16, 2017 at 22:24
  • 2
    @MaxVernon well, I wouldn't want to break the rules. ;-)
    – Brent Ozar
    Feb 17, 2017 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.