question

Kiran 1 avatar image
Kiran 1 asked

Does rebuilding index reduce the physical size of the database?

Does rebuilding index reduce the physical size of the database?

sql-server
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

No. if in the index rebuild, you result in less space being needed, then this simply remains as available free space in the data files.

But it is possible that rebuilding indexes could result in increasing the physical size of the database

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kiran 1 avatar image Kiran 1 commented ·

Thanks! Could you please explain in detail how rebuilding indexes could increase the physical size of the database

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

Various reasons:
You may need space inside the files to create the new index - especially in ONLINE operations where the old index is still available for use whilst the new one is built.
The new index may be larger than the old index, due to fragmentation (defined by the fill factor %)
The rebuild operation is logged, so log file growth could be happening

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.