3

Two tables in our SQL Server DB equate to 500M rows and 350GB of data. Disk space is an issue where we keep backups and the time to execute backups is as well. We plan to truncate these 2 tables.

I understand that to reclaim the disk space, we'll need to shrink the DB after truncation, then do an index rebuild to fix fragmented indexes afterwards. But as we are not sure how long the rebuilds will take (we are spinning up a test server VM in Azure now to test), in the meantime we are looking for incremental gains. After just truncating (before shrink and rebuild), will the backup times and the resulting backup file size (after 7z compression) be reduced dramatically as a result?

1 Answer 1

9

While it's been some time since I dealt with SQL Server backups that weren't compressed by SQL itself, SQL backs ups your data, not your free space.

As stated here

The data contained in a full backup is not necessarily all of the contents of all data files. The backup will only contain the allocated pages from the data files.

TRUNCATE deallocates data pages. If the space consumed by your data drops from 700GB to 350GB, then the size of the backups should be about half what it was as well, and the time to take the backup should indeed be similarly reduced.

1
  • great, that makes sense indeed. Thank you for validating!
    – crichavin
    Aug 16, 2017 at 15:52

Your Answer

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

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