The behaviour of disabling indexes changes depending on if the index is A) nonclustered or B) clustered
For nonclustered indexes
Disabling a nonclustered index removes the index data pages.
Disabling a nonclustered index will deallocate the index pages – the
space is freed in the database.
What remains when disabling the index?
The index definition remains in metadata, and index statistics are
kept on nonclustered indexes.
For clustered indexes
Disabling a clustered index has additional effects. The data in the
table still exists, but will be inaccessible for anything other than a
drop or rebuild operation. All related nonclustered indexes and views
are also unavailable. Foreign key constraints that reference the table
are disabled. Queries against the table will fail.
Since disabling the clustered index disables data access to the table, let's go further with NONCLUSTERED indexes for the sake of reclaiming data.
If we create a table 1M rows and some duplicate indexes
CREATE TABLE dbo.Bla(bla_id int identity(1,1) PRIMARY KEY NOT NULL,
INSERT INTO dbo.bla WITH(TABLOCK) (bla)
SELECT TOP(1000000) 'Bla'
FROM MASTER..SPT_VALUES spt1
CROSS APPLY MASTER..SPT_VALUES spt2;
create index IX_bla on dbo.bla(bla);
create index IX_bla2 on dbo.bla(bla);
create index IX_bla3 on dbo.bla(bla);
create index IX_bla4 on dbo.bla(bla);
The space used is a bit below 75MB:
exec sp_spaceused '[dbo].[bla]'
If we disable all these indexes:
ALTER INDEX IX_bla ON [dbo].[bla]
ALTER INDEX IX_bla2 ON [dbo].[bla]
ALTER INDEX IX_bla3 ON [dbo].[bla]
ALTER INDEX IX_bla4 ON [dbo].[bla]
The space is reclaimed:
As well as any reserved space:
name rows reserved data index_size unused
Bla 1000000 20040 KB 19808 KB 80 KB 152 KB
As such, the database data file will also have additional free space.
Subsequently the only space that you could then try to reclaim is the database data file that grew out.
An example of shrinking a data file:
DBCC SHRINKFILE('DBDataFile',Target_size, TRUNCATEONLY)
Please, only shrink these data files if they will not grow out again. Shrinking and letting it grow out again is terrible.
Before shrinking, read more on it here and here.