SQL Server Filegroup Error – The filegroup is not empty

SQL Server database consists of two types of filegroups, primary and user-defined. Primary filegroup includes primary files. System tables come under the primary filegroup. User-defined filegroups are those filegroups which created by the user (as it is clear from the name) and user can modify it. One file cannot be the member of more than one filegroup. Every database has a default filegroup and when a table or index is created without specifying the filegroup then they allotted to the default filegroup. If database owner or members do not specify the default filegroup then primary filegroup works as default filegroup.

Now it’s time to talk about common error related to filegroups.

Server: Msg 5042, Level 16, State 7, Line 1
The filegroup 'fg' cannot be removed because it is not empty.

First of all check the objects that belong to a filegroup. To check them, use this script:

 SELECT  
   
 fg.data_space_id, fg.name,  
   
 ObjectName = OBJECT_NAME(p.object_id), p.index_id  
   
 ,df.name, df.physical_name, [Size] = df.size*8/1024  
   
 FROM sys.filegroups fg  
   
 LEFT JOIN sys.database_files df  
   
 ON fg.data_space_id = df.data_space_id  
   
 LEFT JOIN sys.partitions p  
   
 ON fg.data_space_id = p.partition_number  
   
 WHERE (p.object_id>4096 or p.object_id IS NULL)  
   

After checking the object by this script, you will be sure about the filegroup of the object.

Solution 1:

To delete a filegroup, first you have to delete the file associated with it. To do so, execute the following query:

 ALTER DATABASE database_name REMOVE FILE file_name  
 GO  
 ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name  
 GO  
   

If you are still unable to remove the filegroup then, use the next solution to fix the issue.

Solution 2:

• Add a new file into the filegroup.

 ALTER DATABASE database_name  
 ADD FILE  
 (  
 NAME=second_data_file,  
 FILENAME = [C:\SQLData\file_name.ndf],  
 SIZE = 1MB,  
 FILEGROWTH = 10%  
 )TO FILEGROUP [filegroup_name];  

• Now empty the first file

 DBCC SHRINKFILE (first_data_file, EMPTYFILE)  

Note: The EMPTYFILE argument moves the file from the selected file to another file of the same filegroup.

• Now delete first data file and second data file.

 ALTER DATABASE DBNAME REMOVE FILE first¬_data_file;  
 ALTER DATABASE DBNAME REMOVE FILE second_data_file;  
 ALTER DATABASE DBNAME REMOVE FILEGROUP filegroup_name;  

Note: You cannot add the new file if filegroup is offline because offline filegroup is a filestream filegroup which doesn’t contain data files.