3

Can a lot of free space in both SQL Server data and transaction files reduce performance when backing up and compressing files?

Our DBA is using Red Gate SQL Backup Pro to backup and compress a database where the data file is 404 GB with 55% (225 GB) free space and the transaction file is 531 GB with 97% (519 GB) free space.

The DBA has assured me that the free space does not affect performance but I wanted a second opinion.

Thanks.

kyletme
  • 441
  • 4
  • 17
  • Why not shrink that transaction log after a backup? – manderson Sep 12 '18 at 20:09
  • @manderson it's a better idea to set the TLOG to the max you ever expect it to grow to. Auto-growths take up resources so ideally you don't want the log growing often... and repeating a shrink, grow, grow, grow, backup, shrink isn't usually advised because you aren't solving any problem. You are temporary regaining space... but this space can't be reallocated since your log will need it again later, leaving you where you started – S3S Sep 12 '18 at 20:31
  • @scsimon got it. I set the prod log here to 2GB, because I know I won't get there by the end of the day... then I back up, and shrink log reclaiming the space. I figured kyletme was letting the tlog continuously grow. I guess the next question is, how long does it take for his/her tlog to get to 531GB? – manderson Sep 13 '18 at 15:37

1 Answers1

2

Bottom line, the backup process doesn't back up empty pages (free space) so the size and performance isn't affected.

Of course, as long as the free space isn't at the page level. That is, as long as you don't have a fill factor < 100%. This kind of "free space" is a real issue that will bloat your backups, restores, DBCC, etc. See this article from Brent on that.

Also, I'm sure Red Gate Pro uses some sort of backup compression which will make your backup smaller.

S3S
  • 24,809
  • 5
  • 26
  • 45