0

I've come across a need to free used space from a large number of disabled indexes.

Is it possible to make SQL Server release the used space from these indexes without a drop/create for each index?

And I'm limited to SQL-Server 2014 functionality :/

  • All the indexes is of cause non-clustered – K.Hanberg Oct 3 at 7:38
0

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

What remains when disabling the index?

The index definition remains in metadata, and index statistics are kept on nonclustered indexes.

Source

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.

Source


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,
                    bla varchar(20));
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]'

index_size
74992 KB

If we disable all these indexes:

ALTER INDEX IX_bla ON [dbo].[bla]
DISABLE;  
ALTER INDEX IX_bla2 ON [dbo].[bla]
DISABLE;  
ALTER INDEX IX_bla3 ON [dbo].[bla]
DISABLE;  
ALTER INDEX IX_bla4 ON [dbo].[bla]
DISABLE; 

The space is reclaimed:

index_size
80 KB

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:

USE databasename
GO

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.

0

No. Script out the disabled and unused indexes then drop them. Save the script of index create statements in your flavor of Source control just in case you need them.

0

I will be more specific. You can use a script like this:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql =
(
    SELECT 'IF EXISTS(SELECT * FROM sys.indexes WHERE name=''' + i.name + ''' AND object_id = OBJECT_ID(''[' + s.name + '].[' + o.name + ']''))      drop index [' + i.name + '] ON [' + s.name + '].[' + o.name + '];  '
    FROM sys.indexes i
         INNER JOIN sys.objects o ON i.object_id = o.object_id
         INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.type <> 'S'
          AND is_primary_key <> 1
          AND index_id > 0
          AND i.is_disabled = 1
          AND s.name != 'sys'
          AND s.name != 'sys'
          AND is_unique_constraint = 0 FOR XML PATH('')
);
EXEC sp_executesql 
     @sql;

Warning: do not ever run scripts from anywhere in production without reviewing the code and testing in test environment!

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.