1

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?

1

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

  • 1
    The script at the link is exactly what I was looking for. Thanks! – Pattison Apr 25 '16 at 22:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.