ELLEN-610393 (1/6/2014)
In the case of a very large table, 1.4 TB, that is not partitioned, what is the least amount of space that will need to be available to build an index? Does it make a difference if the index is clustered [or not]?I am trying to determine before we start the build or rebuild of the index how much free space will be needed 1) in the data files that make up the filegroup where the table resides, 2) in the DB transaction log and 3) in tempdb.
Does recovery mode of the DB matter? if so, which is better?
I'd plan on 1.5TB extra in the MDF file for the Clustered Index. The other indexes should be smaller. Also, unless the database is in BULK-LOGGED or SIMPLE recovery mode, you can plan on similar logfile growth. I do recommend using the SORT IN TEMPDB option which will prevent additional growth of the MDF file.
My recommendation would be that if the Clustered Index is based on an IDENTITY column or the date inserted, you don't need to rebuild the Clustered Index.
It may also be sufficient to "just" do a REORGANIZE on the Non-Clustered indexes which won't require huge amounts of extra room. REORGANIZE does NOT rebuild or reorganize the BTREE but that may not matter.
--Jeff Moden
Change is inevitable... Change for the better is not.