SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


move all indexes to a secondary file group


move all indexes to a secondary file group

Author
Message
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3531 Visits: 860
Comments posted to this topic are about the item move all indexes to a secondary file group

MVDBA
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 920
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/
keppro
keppro
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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?
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 920
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/
hydbadrose
hydbadrose
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 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.
SQL2005-982522
SQL2005-982522
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
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.
repent_kog_is_near
repent_kog_is_near
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 331
What is the logic behind keeping Non-Clustered with Primary and Clustered with Secondary?
Ryan McCauley
Ryan McCauley
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 162
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
AK1516
AK1516
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 1018
is this link working ?????????
Ryan McCauley
Ryan McCauley
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 162
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search