TryCatchFinally.net Some SQL, some .NET, and whatever else

8Feb/102

Migrate database indexes to a new file group

I recently had to mass-migrate all the indexes from a database to a new file group since we'd added some additional storage to our database server. I found this article at SQL Server Central (unfortunately, registration required, so I've included a copy of the original script in the download at the end). While it worked okay, there were some things I didn't like about it:

  • Assumed 90% index fill rate
  • "Moved" indexes were all created as non-unique, regardless of original
  • Fail during index creation left you without an index (drop and then create, with no rollback)
  • Table was un-indexed during the move (index dropped and then created)
  • Script re-created indexes without any "Included" columns, even if original index had them

To address these limitations, I rebuilt the process using that script as a starting point. The new script:

  • Uses 90% fill rate by default, but if the original index had a different rate specified, it will use that
  • Re-creates indexes as unique if the source index was unique
  • Rollback problem resolved - new index is created with different name, old index is dropped, and then new index is renamed, all in a TRY-CATCH block
  • Since the new index is created and then the old one dropped, table indexing remains "online" during the move
  • Migrates "Included" columns in index
  • Updated the script to use SYS views (breaks compatibility with SQL 2000, since SYS is 2005/2008/beyond only)
I welcome any feedback on the script, and would love to know if you see any improvements that should be made.

Download .SQL scripts (contains both Original and Modified scripts)

Comments (2) Trackbacks (0)
  1. Hello,

    This is a terribly old post, but I still find it useful. Just one thing I thought of mentioning is that the script has a filter for heap tables alone(p.index_id = 0). the sys.partitions table has an entry for heap, clustered and nonclustered indexes on a table. That is a counters the filter on sys.indexes table (si.index_id between 2 and 254 – which means all the indexes apart from the clustered) Once I get rid fo the filter on sys.partitions table, the script works well.

    Thanks much and appreciate the effort!

    Regards
    AV

    • I’m happy to hear that it’s helpful! I had originally added the filter to make sure I was moving the indexes and not the data itself. It can definitely be removed if you want to move clustered indexes.

      Thanks!


Leave a comment

Why ask?

No trackbacks yet.