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
Senior SQL Server DBA
Calpine Energy Solutions
Brain Box SQL Mobile DBA
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply