There is insufficient space in the filegroup to complete the emptyfile operation

user_875708 0
Viewed 5100

I am in the process of migrating one of the databases that has multiple filegroups into Azure Sql Server. Azure does not seem to support multiple file groups yet according to DMA(Data Migration Assistant).

Now I would like to merge filegroups back into single file.

By searching on internet I found out the following commands to shrink the filegroup to empty the filegroup. Which I believe just merge the content into Primary filegroup and then the filegroup can be removed.

DBCC SHRINKFILE ('ndfFile', EMPTYFILE);
GO
ALTER DATABASE myDb REMOVE file ndfFile
GO

When I run this I get the following error message:

Msg 2556, Level 16, State 1, Line 2

There is insufficient space in the filegroup to complete the emptyfile operation.

I have checked the Primary file group file and auto growth is enabled

enter image description here

I have taken a full backup and removed the database and restored the database again but again no success.

I have also tried to create a new database with a single file and tried to use import/export to copy the data into new database. However import/export wizard does only create tables but not referential integrity constraint nor primary key etc.

When I create the objects by using Generate Scripts feature to create all objects then I cannot copy the data even the "Insert Identity" option is selected.

Question 1: Is there any way that I can restore MyDb.bak backup file into a single file (and log file)?

Question 2: Is there any way to merge filegroups into single file?

azure-sql-database azure sql-server
2 Answers

You need to move existing data from the other filegroup(s) to the primary filegroup using WITH(DROP_EXISTING=ON) as explained in this article.

After that you can remove the filegroup with an ALTER DATABASE statement.

ALTER DATABASE [DB1] REMOVE FILEGROUP [fg_Secondary]

At the end you will be able to migrate your database to Azure SQL Database.

user_81841390

Finally I got to bottom of this issue. The issue was caused by the Full Text search indexes that are placed into the second file group. It was not easy to identify those with the provided answer.

Running this script below identifies all the full text indexes in the selected database and filegroup for the indexes.

    USE yourdatabasename;
SELECT 
    t.name AS TableName, 
    c.name AS FTCatalogName ,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName,
    f.name as FileGroupName
FROM 
    sys.tables t 
INNER JOIN 
    sys.fulltext_indexes fi 
ON 
    t.[object_id] = fi.[object_id] 
INNER JOIN 
    sys.fulltext_index_columns ic
ON 
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON 
        ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN 
    sys.fulltext_catalogs c 
ON 
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN 
    sys.indexes i
ON 
        fi.unique_index_id = i.index_id
INNER JOIN sys.filegroups f ON fi.data_space_id  = f.data_space_id 
    AND fi.[object_id] = i.[object_id];

Once you have identified all the indexes in the secondary file group then you will need to drop all those indexes and create them again. If you do not specify file group name they will be placed in the default (PRIMARY) file group.

Once the indexes are re-created in the PRIMARY file group then you can run the following script to empty and remove the secondary file group from database:

DBCC SHRINKFILE ('ndfFile', EMPTYFILE);
GO
ALTER DATABASE myDb REMOVE file ndfFile
GO

You can also empty and remove the file in the SSMS properties by following steps below:

Right click database >Tasks>Shrink>Files

Select the second file group in FileGroup dropdown.

Then use the radio button says "Empty file by migrating..." and hit Ok. This will remove the space.

Now file group can be deleted by :

Right click database>Properties

Select FileGroups on the left pane. and select the second file group and remove.

All should be good to go and migrate into Azure Sql Server.

user_8757080
Related