Timeline for Rebuild Index not freeing up space
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 6, 2018 at 23:42 | history | edited | Aaron Bertrand | CC BY-SA 3.0 |
added 14 characters in body
|
| Apr 6, 2018 at 17:32 | history | edited | Aaron Bertrand | CC BY-SA 3.0 |
added 24 characters in body
|
| Apr 6, 2018 at 12:56 | comment | added | Erik Reasonable Rates Darling | I'd add that it may cause additional growth if the rebuild applies a lower fill factor. | |
| Apr 6, 2018 at 12:23 | comment | added | Krishn | Brilliant, thanks for your reply Aaron and sorry for a lack of clarity from me | |
| Apr 6, 2018 at 12:22 | vote | accept | Krishn | ||
| Apr 6, 2018 at 12:15 | comment | added | Aaron Bertrand | Typically an index rebuild will require as much as 2.5x the size of the original index in order to build a copy and then later drop the original. Again, the data file will grow to accommodate the temporary growth, and it frees up the space that it used within the file, but then it will not shrink the file itself for the reasons I already stated. Assuming this table is rather large (or you rebuilt all indexes), this exactly describes what you're seeing. | |
| Apr 6, 2018 at 12:12 | comment | added | Krishn | Hi Aaron, thanks for your reply and sorry for the confusion. I wasn't expecting rebuilding indexes to free up space, my suspicions were that my data file grew and reached capacity due to an application invoked index rebuild procedure and I was curious as to why that space wasn't released once the procedure finished/failed. My data file went from around 114GB to 227GB and 50% of the new file size was unused.Given that Sort in tempdb was off I thought that the indexing routine had grown the database to be able to accomodate for the routine and for some reason not released space | |
| Apr 6, 2018 at 12:06 | history | edited | Aaron Bertrand | CC BY-SA 3.0 |
added 509 characters in body
|
| Apr 6, 2018 at 11:45 | history | answered | Aaron Bertrand | CC BY-SA 3.0 |