Removing a filegroup that contains data.

Occasionally, you may find you have an extra filegroup that is no longer required for whatever reason. In order to remove that filegroup, you must move all data from that filegroup to a new filegroup. How do you do that? Let’s take a look.

First, you can manually do this for each table using the GUI. This tends to be problematic, and in many cases this can cause SSMS to do things “under the covers” which may be a surprise, such as dropping and recreating the table. Inevitably, there needs to be a better, more controlled way of doing this, especially when we’re talking about a lot of items that need to be moved.

I tend to do this kind of thing in an automated way.

I’ve written a stored procedure I use to move tables and indexes. This code has the ability to move heaps (tables with no clustered index), clustered indexes, non-clustered indexes, XML indexes, and spatial indexes. This code does not handle partitioned tables and indexes.

This is the code:

This stored procedure can be called like this:

Cleanup the stored procedure like this:

Once you’ve moved all data out of the filegroup, you can use the following commands to actually remove the filegroup and the files associated with that filegroup:

As with all code, please test this in a non-production environment before using it in production.

Check out the rest of our tools!