move all indexes to a secondary file group

Question

move all indexes to a secondary file group

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
September 29, 2007 at 9:25 pm

#175380

Comments posted to this topic are about the item move all indexes to a secondary file group
MVDBA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

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 Grasshopper Points: 19 More actions · Answer 2

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 SSCrazy Eights Points: 8819 More actions · Answer 3

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 SSCarpal Tunnel Points: 4164 More actions · Answer 4

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 Grasshopper Points: 19 More actions · Answer 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.

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 6

What is the logic behind keeping Non-Clustered with Primary and Clustered with Secondary?

Ryan McCauley Right there with Babe Points: 773 More actions · Answer 7

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 SSCrazy Points: 2705 More actions · Answer 8

AK1516

SSCrazy

Points: 2705

September 28, 2011 at 3:17 pm

#1388124

is this link working ?????????

Ryan McCauley Right there with Babe Points: 773 More actions · Answer 9

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

Arju SSC Veteran Points: 243 More actions · Answer 10

Hi,

Can some one provide links to move tables & indexes to other file group without drop & Recreate them.

Just like to move object to other file group without drop and recreate.

Thanks

Ryan McCauley Right there with Babe Points: 773 More actions · Answer 11

To do this, you have to create a clustered index on the table and specify the new file group for the index - once you've moved it, you can drop the index if you don't need it. Effectively, you'd use this same script, but you'd remove the filter that causes it to ignore clustered indexes so that it migrates those as well.

Ryan