Sample Header Ad - 728x90

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

5 votes
1 answer
823 views
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 ?
Asked by theindianvenom (53 rep)
Nov 9, 2022, 09:08 AM
Last activity: Nov 9, 2022, 02:21 PM