Sql-server – How to remove secondary file group

filegroupssql server

We have a SQL Server database with two filegroups; PRIMARY and SECONDARY, with a single file in each file group.

All the clustered indexes are stored in the secondary filegroup. How can we remove the SECONDARY filegroup?

Best Answer

The clustered index structure is the table in SQL Server.

In order to remove a filegroup, you first need to remove all data from the filegroup. There are several ways of doing that, including using the GUI, like this:

  1. Right-click the table, and choose "Design"
  2. Right-click the "empty" area of the top design panel, and choose "Properties" like this:

enter image description here

  1. In the "Properties" pane (typically shown to the right of the "Design" pane), you open the "Regular Data Space Specification" and change the name of the "Filegroup or Partition Scheme Name" to the name of the target filegroup:

enter image description here

Be aware, this method will result in SSMS generating code that actually drops-and-recreates the table.

An alternate method of doing that would be to rebuild the indexes for the table, specifying the target filegroup in the rebuild command. I've written a blog-post showing the code I use to accomplish that over at SQLServerScience.com

Related Question