#StackBounty: #sql-server #filegroups #sql-server-2017 Cannot remove filegroup with no files associated

Bounty: 50

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.


Get this bounty!!!

Leave a Reply