Cannot remove filegroup with no files associated

Question

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 some hope reading this question on server fault and tried the following:

• Update all statistics
• Drop all statistics which are not related to indexes

However this had no effect. I still have a filegroup with no file associated and the filegroup can't be deleted. I am totally puzzled as this happens in some databases and not in others (with the same structure). When I perform DBCC CHECK FILEGROUP on this empty filegroup I get a bunch of error messages like the following:

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.

DBCC results for 'STORY_TRANSLATIONSCCC'. There are 0 rows in 0 pages for object "STORY_TRANSLATIONSCCC".

Is this normal or does it point to something unusual?

This question might be a duplicate, however I cannot find a working fix for me in other questions on dba.stackexchange. Please have a look at the list what I have already tried. This is identical to the solutions described in Cannot remove unused filegroups.

More details

Maybe it helps to understand what I do before the error occurs. I am planning a migration to a new server. I am currently testing this on a test instance. Databases are restored from the prod server and the recovery model is switched to simple. My goal is to restructure the filegroups and move from a model with one file per filegroup to a model with two files per file group. To achieve that I create new empty filegroups with two files each and move the data over. Unfortunately most objects have LOB Data (XML and binary)...so I leverage partitioning as a helper to move the lob-data as well. At the end all of the data reside in the new filegroups and the old filegroups are empty. Then I remove all the files and remove the respective filegroup as well. The primary filegroup remains and just gets another file added. You can find a sample script in another question of mine. This process works fine but in two databases the files can be deleted but the filegroup not. Surprisingly the structure of these databases ought to be the same as the structure of other databases were no problems were encountered in the process of moving the data and removing the old filegroups.

So here's a list of filegroup and files of the two databases where the issue occurs:

1. CCC_GENTE

before

+-----------------+------------+
| Filegroup       | Filename   |
+-----------------+------------+
| CCC_APPLICATION | CCC_APP    |
+-----------------+------------+
| CCC_ARCHIVE     | CCC_ARCHIV |
+-----------------+------------+
| CCC_AXN         | CCC_AXN    |
+-----------------+------------+
| CCC_GDV         | CCC_GDV    |
+-----------------+------------+
| PRIMARY         | CCC        |
+-----------------+------------+

after

    +-----------------+--------------------------+--------------------+----------------------------------------------------+
| Filegroup name  | Filegroup temporary name | Filename (logical) | Status                                             |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | -                        | CCC_APP            | file removed, filegroup  cannot be removed (error) |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | -                        | CCC_ARCHIV         | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | -                        | CCC_AXN            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | -                        | CCC_GDV            | file and filegroup removed                         |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | CCC                | file renamed to PRIMARY_1                          |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| PRIMARY         | -                        | PRIMARY_2          | new file added                                     |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_1  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_APPLICATION | CCC_APPLICATION_new      | CCC_APPLICATION_2  | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_1      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_ARCHIVE     | CCC_ARCHIVE_new          | CCC_ARCHIVE_2      | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_AXN         | CCC_AXN_new              | CCC_AXN_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_1          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+
| CCC_GDV         | CCC_GDV_new              | CCC_GDV_2          | new filegroup renamed at the end                   |
+-----------------+--------------------------+--------------------+----------------------------------------------------+

I hope that helps a bit. There's also a second database where the filegroup names are different but I leave that out for brevity.

Answer 1

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.name                          AS DataSpaceName
      ,ds.data_space_id                 AS DatSpacID_DataSpace
      ,'-->'                            AS FileGroupNfo
      ,f.name                           AS FileGrpName
      ,f.data_space_id                  AS DatSpacID_FileGrp
      ,f.[type]                         AS FileGrpType
      ,'-->'                            AS DataBaseFilesNfo
      ,df.data_space_id                 AS DatSpacID_DBFiles
      ,df.[type]                        AS DBFilesType
      ,df.name                          AS DBFilesName
      ,'-->'                            AS ObjectNfo
      ,o.[object_id]                    AS OjbID
      ,o.name                           AS ObjName4HeapsClusters
      ,o.type_desc                      AS ObjTypeDesc
      ,'-->'                            AS IndexNfo
      ,i.name                           AS ObjName4Indexes
      ,i.type_desc                      AS IndTypeDesc
      ,i.[object_id]                    AS IndObjID
      ,i.index_id                       AS IndIndID
      ,'-->'                            AS PartSchemaNfo
      ,ps.name                          AS PartSchemaName
      ,ps.data_space_id                 AS DatSpacID_PartSchema
       -- ,au.type_desc                     AS AllocUnitTypeDesc
       -- ,au.data_space_id                 AS DatSpacID_AllocUnit
FROM   sys.data_spaces                  AS ds
       FULL JOIN sys.filegroups         AS f
            ON  ds.data_space_id = f.data_space_id
       FULL JOIN sys.database_files     AS df
            ON  f.data_space_id = df.data_space_id
       FULL JOIN sys.indexes            AS i
            ON  f.data_space_id = i.data_space_id
       FULL JOIN sys.partition_schemes  AS ps
            ON  f.data_space_id = ps.data_space_id
       FULL JOIN sys.objects            AS o
            ON  i.[object_id] = o.[object_id]         
       -- FULL JOIN sys.allocation_units   AS au
       --      ON  au.data_space_id = f.data_space_id

-- If you omit the whole WHERE clause you get an overview of everything (incl. MS objects)
WHERE  o.is_ms_shipped = 0
       -- if you omit the lower AND you'll get all items related to all filegroups
       AND (
               df.data_space_id=(
                   SELECT data_space_id
                   FROM   sys.filegroups
                   WHERE  NAME = @nvObsoleteFG
               )
               OR f.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  ) 
               OR df.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  )
               OR ps.data_space_id=(
                      SELECT data_space_id
                      FROM   sys.filegroups
                      WHERE  NAME = @nvObsoleteFG
                  )
           )

Reference: My personal script

Run it and see if any objects are displayed containing your obsolete filegroup. Go with the data_space_id rather than with the name. The joins are intentionally FULL to catch any "orphaned" references.

Alternatively use this smaller script to quick check for items in the obsolete filegroup:

SELECT o.[name]
      ,o.[type]
      ,i.[name]
      ,i.[index_id]
      ,f.[name]
FROM   sys.indexes i
       INNER JOIN sys.filegroups f
            ON  i.data_space_id = f.data_space_id
       INNER JOIN sys.all_objects o
            ON  i.[object_id] = o.[object_id]
WHERE  i.data_space_id = f.data_space_id
       AND o.type = 'U' -- User Created Tables

Reference: SQL SERVER – List All Objects Created on All Filegroups in Database (SQLAuthority.com)

Answer 2

After four months Microsoft Support found a solution. There was indeed a table referring to this presumably empty filegroup.

The table was identified by the following statement:

SELECT t.[name] FROM sys.tables t
   inner join sys.filegroups f
         on t.lob_data_space_id = f.data_space_id
   where f.name = 'xyz'

After moving the data over to a new table and dropping the problematic table the filegroup was successfully removed. The process of moving the data was : create a new table with the same structure and indexes, copy data via SELECT INTO, drop old table, rename new table (and of course take care of foreign keys if there are any in the whole process))