Required free Disk Space for Index rebuild offline Operations

Question

I have SQL Server 2017 Standard Editon with two Databases.

The current total index size of Database1 is 111 GB and for Database2 is 122 GB.

Since I'm on Standard Edition I can only rebuild the Index offline. We have split the Database-Files on Disk D: and Log-Files on E:. Free disk space on D: is 290 GB and on E: 35 GB.

How can I calculate the required free disk space for offline index rebuild on D: and E:?

For online Index rebuilds, I've found a Microsoft article stating that total space required is approximately 1.2 times the total index size. Is the same estimate true for offline rebuilds? Do I need this free disk space for Database-Files and for Log-Files?

Answer 1

The Offline index rebuild operation creates a copy of the original index, then drops the original, and replaces it with the copy.

However, you should realize this is per index, not per database. The typical index rebuild solution will rebuild indexes one-at-a-time in a serial fashion; so you only need space available on the data drive equal to double the size of the largest index.

Since index rebuilds are a logged operation, the log drive should allow enough space for at least a copy of the largest index to be rebuilt, plus any other concurrent operations that may be occurring. You might consider the use of SORT_IN_TEMPDB = ON for the index rebuild operations, since that allows much of the index rebuild operation to occur in tempdb - if tempdb is located on a different drive that may potentially reduce disk space requirements on your target database's log drive.

Microsoft Docs has some good advice about the transaction log requirements for index operations.

If you regularly run index rebuilds, the database and log file should already have enough unallocated space to run the rebuild operation without requiring these files to grow on disk. This is assuming, of course, that you don't shrink your database and log files after each rebuild.

Answer 2

If you use compression when building the index and it wasn't compressed before, then you may not need 1.2 times the size. You can use sp_estimate_data_compression_savings to make an estimate of the index sizes for row or page compression. Compression will require more CPU when those indexes are used, however. See online help for additional info on compression.