Remove a filegroup from database on SQL Server

In this article we will see how to remove a filegroup from a database on SQL Server.

Each database has a least one filegroup, but it could have many filegroups, if you ever find yourself in the situation where we need to remove a filegroup from a database, you will need to remove all files associated with this filegroup, to delete a file from a filegroup you must move all objects associate with this file to another filegroup.

In the example below, we will show you how it can be done:

Database name :GUID_Test
Filegroup to delete: fg_dat01
Clustered Index: [T_NIVEAU_REF]
No Clustered Index : ADRESSE_TEST

1. Check objects that belong to this filegroup

This script shows us all the database objects and their filegroup:

SELECT o.[name] AS TableName, i.[name] AS IndexName, fg.[name] AS FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = fg.data_space_id AND o.type = 'U'

In our example we have all objects belong to fg_dat01, now we must move all this objects to PRIMARY filegroup, in our case we have three filegroup:

select * from sys.filegroups;

2. Move the object to a Primary filegroup

a. Moving table with a clustered index

Let’s first check the properties of the table that has Clustered Indexes:

exec sp_help '[Test_Guid].[sch_GUID_Content].[T_NIVEAU_REF]'

As you can see that currently the table is on fg_dat01 filegroup. Now we will Move this table to PRIMARY filegroup. We will use the Clustered Index called “PK_T_NIVEAU” to move this table to PRIMARY filegroup.

USE Test_Guid
GO
Create Unique Clustered index [PK_T_NIVEAU] 
ON [Test_Guid].[sch_GUID_Content].[T_NIVEAU_REF](NIVEAU_ID asc)  
WITH (DROP_EXISTING=ON) ON [Primary]
GO

After this query is successfully executed, let’s check the properties of the Table again:

b. Moving table without clustered index

In this part we show you how we can move table to different Filegroups that does not have any Clustered Index.

exec sp_help '[sch_GUID_Content].[ADRESSE_TEST]'

The table [ADRESSE_TEST] does not have any Clustered index, we want to move it to PRIMARY filegroup, to do this we need to create a clustered index on this table (since it does not have any) on PRIMMARY Filegroup.

USE Test_Guid
GO
CREATE CLUSTERED INDEX IDX_ADRESSE_TEST_ID 
ON [sch_GUID_Content].[ADRESSE_TEST] (ADRESSE_ID)
WITH (ONLINE = ON) ON [PRIMARY]
GO

Note that we have created the Clustered Index on [PRIMARY] filegroup. Now, let’s check the property of the table.

At this point, we have moved the table ADRESSE_TEST to the PRIMARY Filegroup. Now if needed, then we can keep the Clustered index or we can go ahead and drop the same.

USE Test_Guid
GO
DROP INDEX IDX_ADRESSE_TEST_ID 
ON [sch_GUID_Content].[ADRESSE_TEST] WITH ( ONLINE = OFF )
GO

3. Empty the data file

We have to empty the file before deleting it.

USE [Test_Guid]
GO
DBCC SHRINKFILE (N'GUID_Content_fg_dat01_01' , EMPTYFILE)
GO

We have 0 used pages, so now we can drop the file and the filegroup.

4. Remove filegroup

USE [Test_Guid]
GO
ALTER DATABASE [Test_Guid]  REMOVE FILE [GUID_Content_fg_dat01_01]
GO
ALTER DATABASE [Test_Guid] REMOVE FILEGROUP [fg_dat01]
GO

I hope that this article helps you.

HALAL Noureddine

Post Views: 8,888