By Aditya Sawant

2019-01-11 18:39:41 8 Comments

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?


@Jonathan Fite 2019-01-11 18:51:27

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?

@Aditya Sawant 2019-01-11 19:16:33

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

@Sean Gallardy 2019-01-11 18:51:14

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

@Tibor Karaszi 2019-01-14 12:40:44

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" :-) ).

Related Questions

Sponsored Content

1 Answered Questions

2 Answered Questions

[SOLVED] Database Maintenance Job and Backup Scheduling

1 Answered Questions

[SOLVED] Will DIFF backup size be reduced if we shrink the data files?

  • 2018-07-04 13:39:16
  • Sandeep
  • 39 View
  • 2 Score
  • 1 Answer
  • Tags:   sql-server

2 Answered Questions

[SOLVED] Rebuild Index not freeing up space

1 Answered Questions

[SOLVED] Will DB backup time and size be decreased after truncate large tables

1 Answered Questions

[SOLVED] Index and Heap table storage on disk

1 Answered Questions

mssql backup from certain date

  • 2016-11-30 10:10:16
  • RPK4488
  • 63 View
  • -1 Score
  • 1 Answer
  • Tags:   sql-server backup

2 Answered Questions

[SOLVED] Reindexing Clustered Primary Key

1 Answered Questions

[SOLVED] Decrease WAL file size for backup

1 Answered Questions

Sponsored Content