Post reply

why space is required for rebuild index. If i m doing rebuilt index what will be increase .mdf or .ldf file

  • December 16, 2015 at 1:36 am

    #323081

    why space is required for rebuild index. If i m doing rebuilt index what will be increase .mdf or .ldf file.

    Please explain internally how sql server will do rebuild or reorg index.

  • December 16, 2015 at 2:31 am

    #1846256

    rajeshjaiswalraj (12/16/2015)

    If i m doing rebuilt index what will be increase .mdf or .ldf file.

    Assuming you're not sorting in tempdb, allow roughly 1 to 1.5 times the size of your largest table in both your data file and log file.

    Please explain internally how sql server will do rebuild or reorg index.

    That's too much to cover in a forum post. There's plenty of information out there, ready for you to search for it. If there's anything in particular you don't understand after reading some of that, please post back.

    John

  • December 16, 2015 at 3:00 am

    #1846261

    Thanks for the reply...

    So you want to say if my mdf file size is 1mb after rebuilt it has to becomes 1.5.

    and my ldf if size is 0.5 mb so it has to becomes 0.75 mb

    But i want to know what its doing internally so my size is increasing.

    And what will be happend for Reorg index.

    Please give me some link if you have.

  • December 16, 2015 at 3:13 am

    #1846263

    rajeshjaiswalraj (12/16/2015)

    So you want to say if my mdf file size is 1mb after rebuilt it has to becomes 1.5.

    No, I'm saying if your largest table is 1MB and you have no free space in your database then you should expect to require an additional 1 to 1.5 MB space in order to carry out your reindexing.

    and my ldf if size is 0.5 mb so it has to becomes 0.75 mb

    No. Assuming as above and that no other transactions are running and that your log is being backed up frequently enough to clear it of other index rebuilds then you should expect to need your log file to be 1.5MB.

    Please give me some link if you have.

    I'm sure your search engine works as well as mine. Try it out, and post back if you genuinely can't find what you're looking for.

    John

  • December 16, 2015 at 5:19 am

    #1846274

    In short, it creates a new copy of the index and then drops the old one, logging the entire process (each new page allocated with contents)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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