Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving existing indexes to new filegroup,sql server 2008 Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 11:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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
Post #1366685
Posted Monday, October 1, 2012 1:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1366720
Posted Tuesday, October 2, 2012 3:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:55 AM
Points: 18, Visits: 444
May below links helps you:
http://technet.microsoft.com/en-us/library/ms175905.aspx
Post #1366921
Posted Tuesday, October 2, 2012 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 2:39 PM
Points: 64, Visits: 513
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.
Post #1367208
Posted Wednesday, October 3, 2012 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1367687
Posted Friday, October 5, 2012 4:07 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 353, Visits: 868
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
---
Post #1369337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse