Rebuild of DB fails, yet size of the DB has doubled

Question

I attempted to rebuild all the indexes of a DB using the query,

USE [DB_Name];
GO
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT '['+OBJECT_SCHEMA_NAME([object_id])+']'+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

The query executed for around 3.5 hours and then it threw the error message, saying that it ran out of disk space, which was not true, but possible that the DB reached its size limit.

But the only issue is that the database actually grew in size, by almost 100% , without any of the indexes being rebuilt. The main reason why I chose to rebuild was that most of the indexes were fragmented beyond 75% .

Now , restoring from a backup is not an option unfortunately, as we have new data being written in already, and it's been hours.

Will another rebuild with sufficient disk space solve it ? If so , do i still go by the thumb rule of 1.5 times the space of current DB ?

J.D. · Accepted Answer · 2022-11-09T14:21:23.343

As discussed in these Microsoft docs on index maintenance, this is typical behavior for index rebuilds. Microsoft recommends having at least twice as much space available because a rebuild stores a new copy of the index that is being rebuilt simultaneously with the old index. Once it's done rebuilding then the old index is dropped but the database files will still remain the same size on disk that they grew to.

See the following from the linked docs:

While an index rebuild occurs, the physical media must have enough space to store two copies of the index. When the rebuild is finished, the Database Engine deletes the original index.

Rebuilding the entire table takes a long time if the index is large, and requires enough disk space to store an additional copy of the entire index during the rebuild.

Despite the aforementioned documentation, nowadays it is well known that rowstore index maintenance is not a helpful operation, in practice. It rarely meaningfully improves performance of your database, regardless of how fragmented your indexes are. It is a waste of I/O operations and disk space (as you've noticed), for little gain afterwards. If you SHRINK your database after, you'll cause your indexes to re-fragment again, being back at square 1, at the cost of additional I/O. And the subsequent actions that do occur from index maintenance, such as updated statistics, can be ran individually so that those gains can be had without the cost of index maintenance.

I've personally only seen it help poorly architected databases that had no clustered indexes, meaning their data was being stored unordered to begin with, in a Heap. Those Heap tables and their nonclustered indexes were severely fragmented, and index maintenance did help improve them. But this is a terrible database design that no one should architect to begin with.

Finally, see AMTwo's helpful cheat sheet on when to rebuild your indexes.

If after all of that, you're still hard struck on doing index maintenance, then maybe you should try reorganizing your indexes instead. Reorganizing fixes fragmentation within the existing index as it goes, and therefore uses much less space to operate as opposed to rebuilding the index.

Rebuild of DB fails, yet size of the DB has doubled

1 Answers1