4

If I delete some huge non-clustered indexes will the backup size of that database also decrease?

I am facing issue with huge backup files and don't have enough disk space to hold those backups.

I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?

4

If you delete 40GB of indexes, then certain backups will be smaller, such as:

  • Full
  • File
  • Filegroup

Some backups won't generally be smaller, such as:

  • Log

Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.

Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.

However, overall, the answer is "Yes, it should be smaller".

  • A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ). – Tibor Karaszi Jan 14 at 12:40
2

YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.

However - If you need to re-create that index then you aren't really buying yourself anything.

Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?

  • I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response.. – Aditya Sawant Jan 11 at 19:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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