Post reply

ALTER INDEX online MS SQL 2005

  • February 12, 2009 at 9:18 am

    #130962

    Hi ,

    is it possible to add fill factor variable in following syntax .?

    Is this statement going to rebuild entire table with default fill factor ? if so then what is default space ?

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (ONLINE = ON);

    Please let me know how to change statement or information

    Thanks

    Ichbin

  • February 12, 2009 at 10:43 am

    #943043

    what version of SQL2005 are you using?

    default fill factor if not changed is 0 which means 100%

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 12, 2009 at 10:48 am

    #943046

    Is that the old-n-good example taken from BOL -AdventureWorks?

    Your answers are: Yes, Yes.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 12, 2009 at 11:09 am

    #943053

    ALTER INDEX ALL ON Production.Product

    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON);

    Thanks!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • February 12, 2009 at 12:18 pm

    #943096

    Thanks guys, 🙂

    I am using 2005 Enterprise edition I have 90 GB table which need to be index on-line.. so I am planning to do it with no downtime.

    One more quick question: Is it possible to rebuild indexes on-line if I have temp DB around 90 GB ?

    Is there any better options I can use ?

    Appreciate your information.

    Thanks

    ichbin

  • February 12, 2009 at 1:17 pm

    #943144

    when rebuilding online simply specify sort_in_tempdb on or off. If a sort is required and you use tempdb just ensure that the location of tempdb has enough space

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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