Cannot remove filegroup with no files associated

I am experiencing some odd error messages on SQL Server 2017 CU3. I am migrating databases and reorganising filegroups. By "reorganising" I mean that I use a stored procedure which creates a partition function and partition scheme on the new filegroup for an object, rebuilds the indexes while partitioning and then removes the partitioning.

At the end I have got some empty filegroups. Their files are removed. Also the filegroup themselves are removed. This works well in most cases. However for two databases I removed the files...have a filegroup left with no file associated but

ALTER DATABASE REMOVE FILEGROUP

throws an error 5042:


The filegroup 'xyz' cannot be removed because it is not empty.

Question

How can I get rid of that empty filegroup...what could be the issue?

I have already read some common issues however they are not present in my system:

-
Checked:

SELECT * FROM sys.partition_schemes;
SELECT * FROM sys.partition_functions;

0 rows... no partitioning objects left in the database

-
UPDATE STATISTICS for all objects in the database


no effect

-
Checks for indexes on filegroup:

SELECT * FROM  sys.data_spaces ds
INNER JOIN sys.indexes i
ON ds.data_space_id = i.data_space_id
WHERE ds.name = 'xyz'

0 rows

-
Checks for objects in filegroup:

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'
AND ds.name ='xyz'

0 rows

I also gave DBCC SHRINKFILE with parameter EMPTYFILE a try prior to removing the file from the filegroup. It does not really make sense to me however I read solutions to describe that as a fix. Had no effect anyway.

I got so

Double-Checking Filegroups in Database

Verify that the filegroup does not have any files left attached by issuing the following command:

use [DB]
go
sp_helpfilegroup

This will produce a list of filegroups:

groupname | groupid | filecount
-----------+---------+-----------
 PRIMARY   | 1       | 1
 xyz       | 2       | 1

...and then for each filegroup listed execute

use [DB]
go
sp_helpfilegroup @filegroupname='PRIMARY'
go
sp_helpfilegroup @filegroupname='xyz'

The output might look like this:

groupname | groupid | filecount
-----------+---------+------------
 xyz       | 2       | 1

....and the second output might be:

file_in_group    | fileid | filename                          | size    | maxsize   | growth  
 ------------------+--------+-----------------------------------+---------+-----------+---------
  xyz_logical_name | 3      | X:\SQL\SQL_DATA\xyz_filegroup.ndf | 5120 KB | Unlimited | 1024 KB

Deleting the Filegroup

If you still have a file associated with one of your filegroups, then the complete command to delete the filegroup's logical file and the filegroup itself would be:

USE [DB]
GO
ALTER DATABASE [DB] REMOVE FILE [xyz_logical_name]
GO
ALTER DATABASE [DB] REMOVE FILEGROUP [xyz]
GO

Filegroup 'xyz' Is Default

If you receive an error message when trying to remove the filegroup's logical file that looks like this:

Msg 5031, Level 16, State 1, Line 88
Cannot remove the file 'xyz_logical_name' because it is the only file in the DEFAULT filegroup.

...then you will have to set the PRIMARY filegroup as the DEFAULT filegroup:

ALTER DATABASE [DB] MODIFY FILEGROUP [PRIMARY] DEFAULT

Filegroup 'xyz' Is ReadOnly

However, if the error message is the following:

Msg 5055, Level 16, State 2, Line 88 
Cannot add, remove, or modify file 'xyz_logical_name'. The file is read-only.

... then you will have to remove the READ_ONLY property on the xyz filegroup:

ALTER DATABASE [DB] MODIFY FILEGROUP [xyz] READWRITE

You should now be able to drop the filegroup's logical file and the filegroup itself.

Open Transactions

If you really don't have a file (logical_name / pyhsical_file_name) associated with the filegroup xyz you are trying to delete, then performing a transaction log backup might release any transactions hindering further deletion of the filegroup.

Dial 911

If all else fails, you might want to consider opening a call with Microsoft.

Metadata Mismatch

Added after further research

Apparently there are cases when the metadata in the database does not reflect the actual location of the objects.

Reference:

• FIX: Metadata inconsistency error after you switch table partitions and drop corresponding files and filegroups (Microsoft Support)

• FIX: Error occurs when you try to drop or delete filegroups or partition schemes and functions in SQL Server (Microsoft Support)

These two cases seem to been resolved with Cumulative Update 3 for SQL Server 2014 SP1 and Cumulative Update 1 for SQL Server 2016 respectively. They don't apply to your situation, but they show that sometimes the metadata can be wrong.

The item that seems to be blocking your filegroup deletion is the index, which might be stored with wrong meta-data.

Possible Solution

Consider rebuilding the index Ref90159CCC which is referenced in the error message.

Cannot process rowset ID 72057594712162304 of object 
"STORY_TRANSLATIONSCCC" (ID 120387498), index "Ref90159CCC" (ID 2), 
because it resides on filegroup "CCC_APPLICATION_new" (ID 8), 
which was not checked.

The following article describes a similar situation and show how the author detected the culprit and resolved the situation.

Reference: SQL Server: switch partition and metadata inconsistency issue (Blog dbi-services.com)

Find Objects Related to Obsolete Filegroup

I rigged up this script to check as much possible hiding places for tables/indexes/partitions/etc. that could be still relating to the dropped filegroup file:

Please replace DEFAULTRO with the name of your obsolete filegroup (e.g. CCC_APPLICATION)

```
/* ==================================================================
Author......: hot2use
Date........: 16.02.2018
Version.....: 0.1
Server......: LOCALHOST (first created for)
Database....: StackExchange
Owner.......: -
Table.......: -
Type........: Script
Name........: ADMIN_Filegroup_Statement_All_Objects.sql
Description.: Checks all objects related to filegroups based on the
............ relationship between the data_space_id ID.
............
History.....: 0.1 h2u First created
............
............
================================================================== */
DECLARE @nvObsoleteFG AS NVARCHAR(50)
SET @nvObsoleteFG = N'DEFAULTRO'

SELECT -- DISTINCT use in conjunction with sys.allocation_units table and objects
'-->' AS DataSpaceNfo
,ds.na

StackExchange Database Administrators Q#196719, answer score: 8