Sample Header Ad - 728x90

Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?

1 vote
2 answers
956 views
If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full instead of 100%, would an index rebuild/reorganize compact the data into fewer data pages, thus resulting in more available space reported in the database? If the previous is true, does it then make sense to reorganize indexes before performing a shrink operation to maximize the space gained by the shrink (followed by another reorganize after the shrink to reduce fragmentation caused by the shrink)? Finally, does DBCC Shrinkfile compact data pages the way a rebuild does, or does it only move data pages around?
Asked by Marcus (89 rep)
Jan 26, 2024, 01:44 PM
Last activity: Jan 26, 2024, 02:06 PM