Problem with calculating an approximate space required for online index rebuild

  • RaufDBA

    SSC Enthusiast

    Points: 181

    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.

  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 995161

    What was the average percent of page fullness both before and after you started?  I can show you how to make better estimates with that information and with knowing what the FILL FACTOR for the index is.  The percent of fragmentation means nothing when it comes to page density.


    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply