3

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?

  • 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 '17 at 16:22
  • None of this has been changed – AzimL Feb 16 '17 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 '17 at 21:38
  • Is the backup drive a local drive, or on shared network storage? – T.H. Feb 17 '17 at 16:01
  • 1
    Back up go from 12hr to 18hr – AzimL Feb 17 '17 at 22:39
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.

  • 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 '17 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 '17 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 '17 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 ;-) – Max Vernon Feb 16 '17 at 22:24
  • 2
    @MaxVernon well, I wouldn't want to break the rules. ;-) – Brent Ozar Feb 17 '17 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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