Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 10 hours ago
    
None of this has been changed – AzimL 10 hours ago
4  
How long was the full backup taking before? Did it go from 12hrs to 18hrs? Or from 1hr to 18hrs? – BradC 5 hours ago

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.

share|improve this answer
    
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 7 hours ago
    
Can't there also be locking issues that would cause the backups to pause while waiting for a lock to release? – CM_Dayton 5 hours ago
    
Not a lock inside the database, but there can be locks on the msdb backup history tables (although that's fairly unusual.) – Brent Ozar 5 hours ago
    
@BrentOzar - I hope you don't mind my edit. Also, I'm starting to think you're trolling me with the disclaimers ;-) – Max Vernon 4 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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