There are times where you need to massively rebuild indexes on some really large databases, after indicated by the
of course.
However, rebuilding indexes, requires also the adequate amount of free disk space that will be used during the rebuild operation (mainly for the sorting process).
Usually the required space is the size of the indexes to be rebuilt plus some more space (more information on Index Disk Space can be found
).
For example, when you have a clustered index and the table size is 50 GB you will need between 50-55 GB of free disk space in order for the rebuild process to run properly.
Imagine a scenario where you need to rebuild 10 clustered indexes and each table to be 50 GB. If you just schedule the job without having in mind disk space you might find your machine running out of disk space very soon! This can have undesirable consequences to the O.S. and any other running processes.
Always, after each large index rebuild, include the following T-SQL statement:
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);
From
Books Online, when the TRUNCATEONLY option is used, it "
releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent."
By the time there's no page movement inside the files, the space that will be used for the index rebuild, it will be returned to the Operating System fast. The only prerequisite is that you run the shrink operation right after the index rebuild.
Let's conclude with pseudo code displaying how the rebuild process should look like:
REBUILD INDEX 1
GO
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);
GO
REBUILD INDEX 2
GO
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);
GO
REBUILD INDEX 3
GO
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);
GO
REBUILD INDEX N
GO
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);
GO
You can also include in the above logic additional maintenance tasks like update statistics, etc.
I hope you find this useful! Cheers!
--
My Latest Projects: