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 databaseno 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:
- 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.