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.
My Latest Projects:
- DBA Security Advisor: Secure your SQL Server instances against security risks.
- In-Memory OLTP Simulator: Easily benchmark SQL Server’s In-Memory OLTP Engine against your custom workload.
- Artemiou SQL Books: Download my latest free books on SQL Server.
- Artemiou Data Tools: See my latest software projects.
Recommended eBooks on SQL Server: