December 1, 2010 at 9:41 am
😀 Thank you -- worked awesome for me!
December 29, 2010 at 12:26 pm
I am having a similar issue and I do have partitioned tables. However, I removed the partitioned tables (stored procedures, schema, etc.) prior to trying to remove the file/file group. When I run the query posted, no results are returned. However, I still cannot delete the file group. Any other suggestions?
December 29, 2010 at 12:59 pm
what is the error you are getting?
December 29, 2010 at 1:01 pm
You might not be able to drop the filegroup because something still remains on it. Here are a couple of queries copied from other locations on the internet that helped me discern what was left on the filegroups:
--Find objects on Filegroups:
---------------------------------------------
SELECT
TableName = OBJECT_NAME(p.object_id),
PartitionNo = p.partition_number,
FileGroup = FILEGROUP_NAME(a.data_space_id)
--,IndexName = i.name
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
ORDER BY TableName, p.partition_number, FileGroup
--Find HIDDEN objects on Filegroups:
----------------------------------------------------------------
select case when indexes.type_desc in ('HEAP','CLUSTERED')
then 'Table-' + indexes.type_desc
else ' NC Index' end as indexType,
rtrim(cast(
case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(schemas.name + '.'
+ indexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
schemas.name + '.' + objects.name as tableName,
coalesce(indexes.name,'') as indexName,
filegroups.name as filegroup
from sys.indexes as indexes
join sys.objects
on indexes.object_id = objects.object_id
join sys.schemas
on objects.schema_id = schemas.schema_id
join sys.filegroups as filegroups
on indexes.data_space_id = filegroups.data_space_id
where objectproperty(indexes.object_id,'IsMSShipped') = 0
order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end
December 29, 2010 at 1:03 pm
Feeling pretty stupid. :rolleyes:
I was getting the file group not empty error. I had not deleted my partition scheme or partition function yet. I did not realize they were tied to the file groups, but sort of makes sense they are. Anyhow, once I removed the scheme and function, I was able to delete the file groups. Thanks for quick reply. (There are maybe five people out of 40 here today. 🙂 )
December 29, 2010 at 1:10 pm
glad I could help!
July 11, 2011 at 10:05 am
Grasshopper you are a star! I have had the same problem on our production DB for weeks. Empty file, delete file, empty filegroup, "The filegroup 'UserData' cannot be removed because it is not empty.
"
Thanks to your script I have found a table that claims to have a PK on the old filegroup. Moving that to a new filegroup had no effect. Dropping the PK had no effect. Then (by adding ,* to the end of your script) I noticed that there was still a NULL entry for the table with "LOB_DATA" in the type_desc column.
There was indeed a column defined as nvarchar(max) and checking the maximum datalength in that column for the table, it showed me 2338 as the maximum used, so I changed the column to nvarchar(2400) instead, and without any other changes I was finally able to drop the empty filegroup. 😀
SSC-Journeyman, I hope this helps you too:
SELECT
TableName = OBJECT_NAME(p.object_id),
PartitionNo = p.partition_number,
FileGroup = FILEGROUP_NAME(a.data_space_id)
,IndexName = i.name
, *
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
ORDER BY FileGroup, TableName, p.partition_number
Now that just leaves me one more tricky filegroup to sort out - does anyone know why my empty PRIMARY filegroup is still taking up 18GB of disk space and will not shrink any further?!?
Cheers,
Greenius
February 20, 2014 at 11:44 pm
with ur work around same problem exists
can any please tell how to remove filegroup from DB.
Before removing i am taking backup of database then removing filegroup for making that production table less heavy .
Also i will be putting restoring that backup if any problem in the database
i am creating a archival engine where there is automatic backup and restoring when needed
February 28, 2014 at 11:04 am
I believe you have to take the following steps first
1. Drop all the tables first
2. Remove file/files from the filegroup
3. REMOVE FILEGROUP
Thanks,
Sponge
January 21, 2016 at 11:15 am
I actually found an easy solution to this fix since I had the same issue.
I was trying to remove the filegroup [fg_LMeterDetail_13] but got the "cannot be removed because it is not empty" error. This filegroup's only sin was being associated with a partition scheme. This filegroup had no data.
So after finding there's no opposite to NEXT USED, I experimented and found that you can issue another NEXT USED but pointing it to the previous filegroup:
ALTER PARTITION SCHEME ps_LMeterDetail
NEXT USED [fg_LMeterDetail_12]
After that, I was able to issue the REMOVE FILEGROUP which then automatically removed it from the partition scheme
ALTER DATABASE [WACOE] REMOVE
FILEGROUP [fg_LMeterDetail_13]
Voila! It worked!
You're welcome everyone.
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
January 16, 2023 at 7:53 pm
You might not be able to drop the filegroup because something still remains on it. Here are a couple of queries copied from other locations on the internet that helped me discern what was left on the filegroups:
Gotta love it. 12 years after you published this and it found what was "stuck". I rebuilt the file group and dropped the index from the table that wasn't supposed to exist any more and dropped the table that also didn't exist (it HAD let me drop the file but not the filegroup previously) and then I was able to drop the file (again) and, finally, the file.
Thank you Amy Walsh!
What really ticks me off is this is 10 years after the fact and MS STILL hasn't fixed this issue!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply