The Filegroup Cannot Be Removed Because It Is Not Empty (Error 5042)

Q: DBCC SHRINKFILE with EMPTYFILE is failing.

Usually because there is nowhere for the pages to go. EMPTYFILE moves them to other files in the same filegroup, so it cannot work on the only file unless the file is already empty. It will also stop on objects it cannot move, which points you back at what is still on the filegroup.

Q: Why does the message say filegroup sometimes and file other times?

Because 5042 is a shared message. In sys.messages the text is The %S_MSG '%.*ls' cannot be removed because it is not empty, and SQL Server fills in which kind of object it is talking about. The fix is the same idea either way: empty it first.

August 13, 2026

Peter Whyte

–

Troubleshooting

Msg 5042 · Level 16 · State 1

The filegroup ‘FG_Data2’ cannot be removed because it is not empty.

⚡Something still lives on it. A filegroup only drops once every object and every file has gone. Find what is on it first, because the answer is often an index or a LOB column rather than the table you were thinking of.

The message is accurate but unhelpful: it tells you the filegroup is not empty without telling you what is in it. Work through it in order and it is a five minute job.

Find Out What Is Actually On It

Start with the objects, not the files. This lists every table and index sitting on the filegroup you are trying to remove:

SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS [schema],
        OBJECT_NAME(i.object_id)          AS object_name,
        i.name                            AS index_name,
        i.type_desc,
        fg.name                           AS filegroup_name
FROM    sys.indexes i
JOIN    sys.filegroups fg ON fg.data_space_id = i.data_space_id
WHERE   fg.name = 'FG_Data2'
ORDER BY object_name, i.index_id;

index_id 0 is a heap and index_id 1 is a clustered index, so either of those means the table itself lives there. Anything higher is a nonclustered index that has been placed on the filegroup separately, which is the case people usually miss.

LOB and partitioned data can also sit there without showing up in the query above:

SELECT  OBJECT_NAME(object_id) AS object_name, filegroup_id, type_desc
FROM    sys.allocation_units au
JOIN    sys.partitions pa ON pa.partition_id = au.container_id
WHERE   au.data_space_id = (SELECT data_space_id FROM sys.filegroups WHERE name = 'FG_Data2');

Move or Drop What You Find

A table moves by rebuilding its clustered index onto another filegroup. This is a physical move of every page, so it needs room and a window:

CREATE CLUSTERED INDEX PK_Orders_Archive
    ON dbo.Orders_Archive (OrderID)
    WITH (DROP_EXISTING = ON)
    ON [PRIMARY];

A nonclustered index is simpler, since it can be dropped and recreated wherever you want it. A heap has no clustered index to rebuild, so give it one, move it, and drop it again if the heap was deliberate.

Then Empty and Remove the Files

Objects gone is not the same as empty. The files still belong to the filegroup and still hold pages, so empty them first and only then remove them:

DBCC SHRINKFILE (YourDatabase_Data2, EMPTYFILE);   -- moves pages out
ALTER DATABASE YourDatabase REMOVE FILE   YourDatabase_Data2;
ALTER DATABASE YourDatabase REMOVE FILEGROUP FG_Data2;

EMPTYFILE moves the remaining pages to other files in the same filegroup. If this is the last file in the filegroup there is nowhere for them to go, and the shrink will fail rather than silently drop anything. That is the point at which you go back and check the object queries above again.

Microsoft’s reference covers DBCC SHRINKFILE and ALTER DATABASE file and filegroup options in full.

The Order That Works

Every failed attempt at this comes from doing it in the wrong sequence. The order is: find the objects, move or drop them, empty the files, remove the files, remove the filegroup. Check as you go rather than at the end:

SELECT  fg.name AS filegroup_name,
        COUNT(DISTINCT i.object_id) AS objects_still_here,
        COUNT(DISTINCT f.file_id)   AS files_still_here
FROM    sys.filegroups fg
LEFT JOIN sys.indexes i        ON i.data_space_id = fg.data_space_id
LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
WHERE   fg.name = 'FG_Data2'
GROUP BY fg.name;   -- both zero before REMOVE FILEGROUP will work

Common Questions

I dropped the table and it still will not remove.

The files are still there. Dropping objects and removing files are two separate steps, and the filegroup counts as non-empty while it still owns a file. Run REMOVE FILE for each file, then remove the filegroup.

Can I remove PRIMARY?

No. PRIMARY holds the database metadata and cannot be removed or left empty. If your goal is to get user data off PRIMARY, that is done by moving objects elsewhere and changing the default filegroup, not by removing it.

DBCC SHRINKFILE with EMPTYFILE is failing.

Usually because there is nowhere for the pages to go. EMPTYFILE moves them to other files in the same filegroup, so it cannot work on the only file unless the file is already empty. It will also stop on objects it cannot move, which points you back at what is still on the filegroup.

Is there any risk of data loss?

Not from the error itself, which is SQL Server refusing to do something destructive. The risk is in the steps you take to get around it. Rebuilding a clustered index moves real data, and EMPTYFILE relocates pages, so take a backup first and do it in a window.

Why does the message say filegroup sometimes and file other times?

Because 5042 is a shared message. In sys.messages the text is The %S_MSG '%.*ls' cannot be removed because it is not empty, and SQL Server fills in which kind of object it is talking about. The fix is the same idea either way: empty it first.