Moving existing indexes to new filegroup,sql server 2008

  • Hi,

    Can anyone of you please guide me how to move indexes (Primary filegroup) to new Index filegroup.

    I have done for one table manually.

    1. drop constraints

    2. adding index to new filegroup using alter statement

    3. Enable constraint.

    Since this is very big database,so some other process I have learn to do this task. Is there any way to reindexes using alter statement for all tables. Please suggest me.

    Thanks, Kumar

  • Are you trying to move all your indexes to another filegroup? What about clustered indexes? Move those too?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May below links helps you:

    http://technet.microsoft.com/en-us/library/ms175905.aspx

  • Hi, I got requirement to move all indexes from primary to new filegroups.

    Is there any challange will face while moving cluster index to new filegroups.

  • Challenges will mainly depend on the amount of data and what you can allow for downtime, or if you can do an online rebuild.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • One thing I saw on MSDN and worth noting:

    "Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. "

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    ---

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

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

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