Search code examples
sql-serverbackupredgate

SQL Server Backup with Free Space

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.

Solution

  • 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.