move all indexes to a secondary file group

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

    MVDBA

  • 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/

  • 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?

  • 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/

  • 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.

  • 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.

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

  • 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

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

  • 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

  • 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

  • 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