I'm looking at implementing File Groups on an existing database that's roughly 600GB. The largest hurdle I have is actually setting this up in production. I need the database to still be available but moving some of the tables will take a considerable amount of time. In development it took nearly 5 days to move all the indexes to their new homes and then shrink the database.

So what is the best way to implement file groups on a large database where you need to keep the database live?

  • You could try doing the filegroup migration slowly over time. As an index gets sufficiently fragmented, you can drop and re-create it in the new filegroup during a maintenance window. For the tables, you may have to resort to building a new schema and creating a second set on the new filegroup and backfill the data. Obviously you'd need adaquate disk space for that. – Queue Mann Apr 8 '15 at 17:46
  • How could I migrate a large table slowly? – nikolifish Apr 8 '15 at 18:22
  • are you using SQL Partitioning? If not, can you? You can create a new partition and have all the new data go into that partition. Then over time you could copy the old partition over to the new filegroup but it'll eat up extra space. – Ali Razeghi Apr 8 '15 at 18:24
  • Aren't the FIlegroups Sql Partitioning? – nikolifish Apr 8 '15 at 18:27
  • You could use triggers on the existing table to handle the existing DML to also occur on the new table. Then have a job that backfills the new table several million rows at a time. Then when the job completes, the two tables are in sync. Then you make the switch. – Queue Mann Apr 8 '15 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.