0

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below:

         total size          used             free
mdf       13261 MB         13261 MB             0 MB
ldf        9697 MB            25 MB          9672 MB

After rebuilding of the largest index only with fragmentation percentage equivalent to 91% (size of index was approx 5858 MB) mdf and ldf files became as below:

         total size           used             free
mdf       16854 MB          10061 MB         6792 MB
ldf        9697 MB            363 MB         9334 MB

The question is, if the size of the largest index was 5858 MB, and according to some books and resources on the internet which state that total size needed for rebuilding an index is equivalent to approx 120% of the largest index or maybe double size, then total size of mdf file after rebuilding should be just over 20000 MB, shouldn't it?

I calculated that like 5858*120%=7029 MB, and finally as we do not have space in mdf file then autogrowth will happen and final size of mdf will be 13261 MB + 7029 MB = 20.29 GB.

Here I use Ola's script for rebuilding an index:

EXECUTE dbo.IndexOptimize
@DATABASES = 'TestDB',
@FragmentationLow = NULL,                                                                           
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'ALL_INDEXES',
@MinNumberOfPages = 1000;

The index which is rebuilt is clustered index and key column is of INT data type.

Improve this question
13
  • Rebuilding online with no concurrent transactions is unusual. Typically SQL Server has to track all the changes to the index during the rebuild and apply them to the rebuilt index. My guess is that that accounts for the lesser space utilization.
    – David Browne - Microsoft
    Nov 25, 2019 at 0:04
  • it is Test Server.
    – Rauf Asadov
    Nov 25, 2019 at 4:45
  • Did you check this thread - dba.stackexchange.com/questions/208013/…
    – Learning_DBAdmin
    Nov 25, 2019 at 6:39
  • 1
    What index is this? Clustered / Nonclustered? What datatype(s) are the key columns of the index? Is the index on a heap or not?
    – Randi Vertongen
    Nov 25, 2019 at 8:36
  • 1
    You wrote: if the size of the largest index was 5858 MB. My question to you is: How did you calculate the size of your largest index?
    – John K. N.
    Nov 25, 2019 at 9:06

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.