locked
Rebuild only Fragmented Indexes RRS feed

  • Question

  • Can any please help provide me a Script on :

    1.Rebuild only Fragmented Indexes...

    I have a Database mirroring on 1 database  and when i do rebuild indexes(ALL) the log is growing and taking too much time to restore the log on Mirrored server ..so I would like to Rebuild only Fragmented Indexes ..

    Please help me ...

    Thanks

    Tuesday, July 31, 2012 5:20 PM

Answers

  • Hello,

    The script query all fragmented indexes with a fragmentation trashold of at least 10% and min. 2 pages. It prints out which table is rebuild and which fragmentation in percentage have had.

    If you don't get any output, then may because your index are not fragmented.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Iric Wen Thursday, August 9, 2012 2:12 AM
    Wednesday, August 1, 2012 4:32 AM

All replies

  • Hello,

    Please have a look at MS TechNet ScriptCenter: Defrag fragmented Indexes , just change Defrag to Rebuild.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Naomi N Tuesday, July 31, 2012 6:38 PM
    Tuesday, July 31, 2012 5:26 PM
  • Hi, Thanks for the script ..before running the script and after running the script i dont see any change ( i did change Defrag to rebuild).

    Can you please explain why so?

    Tuesday, July 31, 2012 5:31 PM
  • Hi, Thanks for the script ..before running the script and after running the script i dont see any change ( i did change Defrag to rebuild).

    Can you please explain why so?

    How do you find the fragmentation of you indexes?

    How many pages are there for the index? If the page count is less than 1000, then there would not have much difference doing so.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, August 1, 2012 4:13 AM
  • Hello,

    The script query all fragmented indexes with a fragmentation trashold of at least 10% and min. 2 pages. It prints out which table is rebuild and which fragmentation in percentage have had.

    If you don't get any output, then may because your index are not fragmented.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Iric Wen Thursday, August 9, 2012 2:12 AM
    Wednesday, August 1, 2012 4:32 AM
  • Hi,

    if enough of us vote on this https://connect.microsoft.com/SQLServer/feedback/details/490434/improve-maintenance-plans-to-rebuild-reorg-indexes-based-on-fragmentation-percent hopefully Microsoft will finally add this much requested feature.

    Wednesday, August 1, 2012 7:29 AM