5

I had a situation yesterday where my indexes were rebuilt and database size doubled in size (50 % of the new size was unused). Sort in tempdb is set to off and I get the impression that this rebuild caused it (index rebuild not in tempdb).

Is there a reason for the rebuild procedure to hold onto disk space after completion/potential failure?

11

If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works.

In fact, rebuilding can even lead to larger data files on disk, as new pages and extents are allocated to hold the copy of the data. When the old pages are dropped from the index, they're not actually removed from anything, they're just deallocated.

You've rebuilt your indexes and saved some space inside the data file. This means that as you add more data (to this and other tables), the data file doesn't immediately have to grow to make room for the new data.

This is a good thing. Growing a file is an expensive, blocking operation, and you want to minimize this. Shrinking the file to get back some disk space temporarily seems like a lost cause. The data file is just going to grow again later, in which case, what are you going to do with that disk space you got back for a little while? Lease it out to someone and then evict them when your database has to grow again? Just leave it at the size it has grown to now, because unless you mark it as read-only, it will more than likely grow to that size again.

The data file is just a container. Left alone, it will only ever grow. It won't shrink just because you've deleted some rows or rebuilt some indexes or dropped a table. SQL Server doesn't release that disk space and shrink the file for you for the same time it won't release memory back to the operating system just because you've dropped an index that happened to be loaded into the buffer pool: it assumes you're going to use that space / memory again, and knows that it's expensive to keep releasing it and re-claiming it. So it just holds onto it.

And, in almost all cases, you're going to want it that way. If you really need to reclaim some space that you know you won't use again, see this post.

  • 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 – Krishn Apr 6 '18 at 12:12
  • 1
    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. – Aaron Bertrand Apr 6 '18 at 12:15
  • Brilliant, thanks for your reply Aaron and sorry for a lack of clarity from me – Krishn Apr 6 '18 at 12:23
  • 2
    I'd add that it may cause additional growth if the rebuild applies a lower fill factor. – Erik Darling Apr 6 '18 at 12:56
1

If you can afford to rebuild offline, then it won't take up as much space. Even with sort_in_tempdb on, you'd still need at leastcan 2x the space as a result. Online rebuilds require maintaining the original index while rebuilding a new copy, then a shorter latch at the end to swap it out and apply interim changes. Offline rebuilds drops the index and recreates it, but it will cause blocking on the table in the meantime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.