Handling Disk Space Issues During Heavy Index Rebuild Operations

Handling Disk Space Issues During Heavy Index Rebuild Operations

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
There are times where you need to massively rebuild indexes on some really large databases, after indicated by the relevant analysis 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 here).

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.

To this end, before deploying an index rebuild operation you first need to make sure that you have an adequate amount of free disk space.

But which disk drive should you monitor? The TempDB drive? The user database drive?

When you design an index rebuild operation, you typically have the option to sort the rebuild results in the “TempDB” system database. If you use this option then you will need to make sure that there is enough disk space on the disk onto which the TempDB database is located.

If you do not choose to sort the rebuild results in the “TempDB” database, then you will need to make sure that there is enough disk space on the disk onto which the user database is located. Of course, you should always have disk space available (always allow for some GB to be available) for TempDB as it is used in most of the SQL Server operations. However, in this case you need to focus on the drive where the database is located on.

Last but not least, a practice which I find useful because it helps avoiding disk space issues when running heavy index rebuild operations is the following:

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:


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

2 thoughts on “Handling Disk Space Issues During Heavy Index Rebuild Operations

  1. Jeff

    DBCC SHRINKDATABASE will also shrink the logfile. It would probably be better to just use DBCC SHRINKFILE with the TRUNCATEONLY option.

    I will say that I've not had much luck with this option because there's frequently the data of other tables or indexes at the logical end of the file. It may, however, be just what the Doctor ordered for the individual files of partitioned tables.

  2. Artemakis Artemiou [MVP]

    Hi Jeff,

    I agree that you can also use DBCC SHRINKFILE with TRUNCATEONLY.

    However, as the index rebuild operations move pages inside the data file and they will require log space for storing the temp results (unless you use TempDB for that), DBCC SHRINKDATABASE will release space from both data and log files because, in this example, it runs right after the index rebuild for each database.