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

scripts to move indexes to diffrnt file groups Expand / Collapse
Author
Message
Posted Friday, December 23, 2011 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 12:05 AM
Points: 2, Visits: 50
need some assistance regarding scripts...The requirement as follows
-------------------------
Move all non-clustered indexes to INDEX file group
Move all user (U) type tables and clustered indexes to DATA file group

For the above,we have 4 schemas and 150+ tables in the database,can we move all indexex/tables at a time using scripts
Post #1226169
Posted Friday, December 23, 2011 5:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
Have you checked the script library here or a google search? There should be something to get you started. It's not trivial though.

One question though. Why are you doing this? What's the reason behind moving nonclustered indexes to another filegroup.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1226172
Posted Friday, December 23, 2011 6:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 12:05 AM
Points: 2, Visits: 50
Checked in script library,but not found the script
Post #1226201
Posted Friday, December 23, 2011 11:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:06 PM
Points: 198, Visits: 1,129
You can either drop the existing index and create the new one on the new file group or use WITH (DROP_EXISTING = ON), instead of explicity dropping it first.
Post #1226347
Posted Saturday, December 24, 2011 12:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
janardhan4891 (12/23/2011)
Checked in script library,but not found the script


Have another look. I remember seeing a couple of these scripts in there.

Just curious, and like Gail asked, why are you looking to make these moves?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1226418
Posted Saturday, December 24, 2011 3:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 23, 2012 8:09 AM
Points: 70, Visits: 157
Here are the scripts.

http://www.sqlservercentral.com/scripts/31541/
http://www.sqlservercentral.com/scripts/63620/
http://www.sqlservercentral.com/scripts/63620/


java
Post #1226446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse