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.
Best Answer
Double-Checking Filegroups in Database
Verify that the filegroup does not have any files left attached by issuing the following command:
This will produce a list of filegroups:
...and then for each filegroup listed execute
The output might look like this:
....and the second output might be:
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:
Filegroup 'xyz' Is Default
If you receive an error message when trying to remove the filegroup's logical file that looks like this:
...then you will have to set the
PRIMARY
filegroup as theDEFAULT
filegroup:Filegroup 'xyz' Is ReadOnly
However, if the error message is the following:
... then you will have to remove the READ_ONLY property on the
xyz
filegroup: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.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
)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 intentionallyFULL
to catch any "orphaned" references.Alternatively use this smaller script to quick check for items in the obsolete filegroup:
Reference: SQL SERVER – List All Objects Created on All Filegroups in Database (SQLAuthority.com)