Post reply

Rebuild index

  • March 19, 2015 at 3:35 pm

    #305947

    hi ,

    Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).

    Thank you advance

  • March 19, 2015 at 4:28 pm

    #1785241

    May I know why shrinking is not an option here? Is it because db in simple recovery mode

  • March 19, 2015 at 4:32 pm

    #1785242

    Shrinking will fragment indexes

  • March 19, 2015 at 9:51 pm

    #1785266

    Barcelona10 (3/19/2015)

    hi ,

    Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).

    Thank you advance

    No. Not directly. And the indirect method is a huge PITA.

    Are you trying to do this on file groups other than the PRIMARY filegroup, a partitioned table with multiple file groups, or just the PRIMARY filegroup?

    You could try a shrink with truncate only (which will not re-frag your indexes) but you have to get pretty lucky there.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 20, 2015 at 9:58 am

    #1785404

    Shrink does not always fragment indexes; it certainly does not always fragment every index. You can shrink as much as you really need and then rebuild/reorg any index that needs it.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • March 20, 2015 at 10:00 am

    #1785406

    Barcelona10 (3/19/2015)

    Shrinking will fragment indexes

    I'm not saying shrinking is a good idea, but if you rebuild your indexes after shrinking, they will not be fragmented.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 20, 2015 at 1:08 pm

    #1785456

    After rebuilding indexes(PRIMARY file group) shrinking fragmenting my indexes(at least i checked 10-15 of them).

    rebuild----defrag

    shink------frag.

    rebuild----defrag

    shink------frag......

    I have not tried partial shrinking then just do index reorganize(instead of rebuilding).

    I'll try and i'll post the results.

    thank you all

  • March 20, 2015 at 1:10 pm

    #1785458

    rebuild----defrag(space issue)

    shink------frag.

    rebuild----defrag(space issue)

    shink------frag.....

  • March 20, 2015 at 1:33 pm

    #1785464

    Barcelona10 (3/20/2015)

    rebuild----defrag(space issue)

    shink------frag.

    rebuild----defrag(space issue)

    shink------frag.....

    DROP DATABASE ---- No issues!

    Alex Suprun

  • March 21, 2015 at 12:42 pm

    #1785519

    Alexander Suprun (3/20/2015)

    Barcelona10 (3/20/2015)

    rebuild----defrag(space issue)

    shink------frag.

    rebuild----defrag(space issue)

    shink------frag.....

    DROP DATABASE ---- No issues!

    Well that just made me LOL. 😎

  • March 21, 2015 at 2:46 pm

    #1785526

    Barcelona10 (3/20/2015)

    rebuild----defrag(space issue)

    shink------frag.

    rebuild----defrag(space issue)

    shink------frag.....

    Just to be sure, if you shrink the database, do a REORGANIZE instead of a REBUILD (reorg is done in place rather than separately so causes nearly no growth), and the a shrink with "TRUNCATE ONLY", you should be good. Well, except maybe for the B-TREE of the indexes. You'd have to test to see if your queries require the B-TREE to be nicely defragged, as well. In many cases, the answer will be "it didn't matter".

    Just remember that REORGANIZE is ALWAYS full logged. Unless you're in the SIMPLE recovery model, Reorging the whole database is like copying the whole database to the log file and more.

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 23, 2015 at 10:37 am

    #1785768

    Thank you very much Jeff.

    I'll test your suggestions as well.

Viewing 12 posts - 1 through 11 (of 11 total)

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