Typical wisdom is 1.5x index size, plus a little fudge depending on fill factor and how full your current index pages are (in most cases I would expect it to be slightly less than 1.5x). But I would definitely not cut it close. If you don't have 150GB free on your drive, I would find a way to do it elsewhere. I don't know if WITH DROP_EXISTING
will help here, somehow I don't think so.
If you are talking about the file system space and releasing it back to the Operating System, then you will have to manually do this operation.
First find out how much free space you have in each file:
use YourDatabase;
go
;with file_cte as
(
select
name,
physical_name,
size_mb =
convert(decimal(11, 2), size * 8.0 / 1024),
space_used_mb =
convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
from sys.database_files
)
select
name,
physical_name,
size_mb,
space_used_mb,
free_space_mb = size_mb - space_used_mb,
space_used_percent =
convert(decimal(5, 2), space_used_mb / size_mb * 100)
from file_cte;
Then if you are absolutely sure that you need to shrink a database file, you can use DBCC SHRINKFILE().
To shrink your file, an example would be like this:
dbcc shrinkfile(YourDataFileName, <target_size_mb>);
But... only shrink your database files if you absolutely must. If your files are going to re-consume that same space in the future, it doesn't make sense to shrink your files. A necessary read is Why you should not shrink your data files by Paul Randal.
Best Answer
The reason is that index rebuild means creating a new index under covers and then dropping the old index. I.e., while the operation is running you need twice the amount of storage available. If that isn't doable for you, you have basically two options:
Do it yourself in the opposite order: Drop first and then create.
Or do REORGANIZE instead of REBUILD.