Disk Space Requirements for Index DDL Operations

Are you watching your disk space during the indexes operations where these database objects are stored? Recently I was stumped on a database that is only 5GB had reindexing process failure due to 10% of disk free space was available.

For your information you need to consider that one of the reason behind slow indexes process or rebuilding process is due to Disk Space requirements for index DDL operations. SQL Server 2005 uses fair amount of disk space when you create, rebuild, or drop indexes. In any case inadequate disk space can degrade performance or even cause the index operation to fail or even it might take longer time to complete if the space varies on time to time basis.

Well, not all of the index operations will not use the additional disk space such as DROP INDEX, also when you are dropping a clustered index offline without specifying the MOVE TO clause and nonclustered indexes do not exist. Another releative DCL statement such as CREATE TABLE.

Other DDL operations require additional disk space during the operation to store the new/modified index structure. The process is when a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. So unless the new index creation process is complete the old structure will still hold the disk space and will only be deallocated until the index creation transaction commits. DDL operations such as CREATE INDEX WITH DROP_EXISTING, ALTER INDEX REBUILD, also when adding any constraints such PRIMARY KEY or UNIQUE, finally while performing the clustered indexes DROP INDEX statement

During these operatiosn Optimizer needs a plan that avoids any sorting of indexes, hence the temporary disk space is required. So for such sorting occurs one new index at a time, for instance when you are attempting to rebuild clustered index with a single statement, those indexes are sorted one after the other and this includes associated non-clustered indexes too.

As per the most view blog here SQL2005-IndexOptimization-BestPractices, if you are using SORT_IN_TEMPDB ON then that index must handle in TEMPDB database. Eventually this needs considerable amount of temporary disk space that is used to create an index, it may reduce the time that is required to create an index when TEMPDB is on a set of disks different from the user database. If SORT_IN_TEMPDB is not used (OFF) then only disk space is used for sorting for the new index structures is required. Inspite of such options you use whenever you perform index operations online, additional temporary disk space is required by SQL Server engine optimizer.

Finally to accomplish or be aware of requirements for such operations refer to Index Disk Space Example article.