1

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?

3

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.

  • For clarity if i have on an SQL-Server two databases. Database1 with highest index size 35 GB and database2 with highest index size 29 GB then i only need disk space 35GB*2 = 37 GB free disk spcae on E: drive /Logs? Is that calculation correct? – Steve_Mueller May 28 '18 at 18:03
  • If the index rebuild operation for both databases is scheduled for the same time period, you'd need 35 + 29 = 74GB free space on the data drive, and I'd want at least that much free on the log drive. By "free space", I mean the combination of free space inside the database files and free space on each drive. Having said that, a lot depends on the specifics of how the rebuild job is built. For instance, if you wrap multiple rebuild operations inside a single transaction, you'll need enough space for a copy of every index, not just the largest. – Max Vernon May 29 '18 at 14:40
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.