Moving existing indexes to new filegroup,sql server 2008

Question

Moving existing indexes to new filegroup,sql server 2008

srimkumarp-720356

Ten Centuries

Points: 1300
More actions
October 1, 2012 at 11:45 am

#263080

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

db_expert_pradeep SSC Veteran Points: 294 More actions · Answer 2

May below links helps you:

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

srimkumarp-720356 Ten Centuries Points: 1300 More actions · Answer 3

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.

GSquared SSC Guru Points: 260824 More actions · Answer 4

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

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

---

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