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 12»»

move all indexes to a secondary file group Expand / Collapse
Author
Message
Posted Saturday, September 29, 2007 9:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:48 AM
Points: 2,274, Visits: 767
Comments posted to this topic are about the item move all indexes to a secondary file group

MVDBA
Post #404559
Posted Monday, August 18, 2008 5:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
I really like the script, but it does not maintain the unique portion of indexes which makes it hard to use in some cases.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #554679
Posted Saturday, August 30, 2008 5:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 11:16 PM
Points: 1, Visits: 73
Thanks a lot to the author!
One problem with the script: it doesn't recognize indexes with included fields. Does anybody know what system table(s) contain this information? Better yet, is there any kind of API for Management Studio's scripting?
Post #561682
Posted Sunday, August 31, 2008 9:47 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
keppro (8/30/2008)
Thanks a lot to the author!
One problem with the script: it doesn't recognize indexes with included fields. Does anybody know what system table(s) contain this information? Better yet, is there any kind of API for Management Studio's scripting?


It is in sys.index_columns, with the included columns marked by a 1 in the is_included column.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #561773
Posted Friday, October 10, 2008 2:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
I loved the script. I wish the author or some cold post the reverse process like moving non-clustered indexes from secondary to primary with clustered indexes.
Post #584257
Posted Wednesday, January 07, 2009 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 15, 2009 3:36 PM
Points: 1, Visits: 5
Got an error about foreign key. Any idea?

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index 'xxxxxxxxxxxxxx'. It is being used for FOREIGN KEY constraint enforcement.
Post #631692
Posted Monday, October 05, 2009 9:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 08, 2011 1:21 PM
Points: 264, Visits: 331
What is the logic behind keeping Non-Clustered with Primary and Clustered with Secondary?
Post #798293
Posted Monday, February 08, 2010 10:44 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:30 PM
Points: 182, Visits: 145
I've updated this script to do a few additional things, like

- Properly re-create unique indexes
- Handle Included columns
- Do "online" index moves w/error handling - creates new index, drops old, then renames
- Uses existing fill factor if the source index explicitly defines it

I've posted the script at my blog - I'm interested if anybody else has feedback or something they'd like to see added.

http://www.trycatchfinally.net/post/2010/02/08/Migrate-database-indexes-to-a-new-file-group.aspx



Ryan
Post #861882
Posted Wednesday, September 28, 2011 3:17 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 12:51 PM
Points: 132, Visits: 905
is this link working ?????????
Post #1182878
Posted Wednesday, September 28, 2011 4:21 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:30 PM
Points: 182, Visits: 145
I've fixed the redirection on my blog, so you can view it now. I must have changed the layout at some point and not set up this forwarding page properly.

Sorry for the confusion.



Ryan
Post #1182896
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse