May 19, 2009 at 8:09 pm
Hello all,
My question is whether a rebuild index would affect the size of a full backup. That is, would a rebuild index reduce further backup sizes after the rebuild index is completed. Please note that I am not referring to data files or the shrink operation.
If this is true, in what circumstances can it be true. Please throw some light.
Thanks and Best Regards
Manzer
May 19, 2009 at 9:44 pm
If the indices are very fragmented then an index rebuild will generally reduce the number of used pages in your database somewhat, because it will compact your indices (and data for any table with a clustered index) down to the original fill factor. How large that reduction is depends on how much unused space there is in the indices compared to the defined fill factor.
It won't make much difference, if any, if the data is entered in order (eg. if all tables have clustered indices on date fields) and there's little updating of indexed fields or deletion of rows. It's even possible for the number of used pages to increase slightly if your indices have a low fill factor and you've inserted lots of random data.
The short answer is, as always, "it depends". In most cases I'd expect the difference to be negligible, maybe 1-2%.
May 19, 2009 at 11:12 pm
As said by glen. Rebuilding indexes usually will not benefit much in terms of space. Rebuilding will help to retrieve more data in less pages thus will improve performance. So major benefits will be in terms of performance only. And shrink operation for any database will have no effect on backup size.
DBDigger Microsoft Data Platform Consultancy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply