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?
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.
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.
