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.
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:
SELECT * FROM sys.partition_schemes; SELECT * FROM sys.partition_functions;
0 rows… no partitioning objects left in the database
UPDATE STATISTICSfor all objects in the database
- 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'
- 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'
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.