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 01, 2012 11:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 22, 2013 2:52 AM
Points: 64, Visits: 495
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 01, 2012 1:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 02, 2012 3:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 4:37 AM
Points: 18, Visits: 420
May below links helps you:
http://technet.microsoft.com/en-us/library/ms175905.aspx
Post #1366921
Posted Tuesday, October 02, 2012 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 22, 2013 2:52 AM
Points: 64, Visits: 495
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 03, 2012 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 05, 2012 4:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
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