move all indexes to a secondary file group

  • MVDBA

    SSC-Insane

    Points: 20310

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

    MVDBA

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    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

    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

    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

    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

    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

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

  • Ryan McCauley

    Right there with Babe

    Points: 773

    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

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

  • Ryan McCauley

    Right there with Babe

    Points: 773

    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

    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

    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

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

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