Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Remove a Filegroup Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 11:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 11:29 PM
Points: 96, Visits: 132
O~ bug? Maybe~

There have been no files in the filegroup, in other words, the filegroup that i want to delete is empty.
BUt unfortunately, I can't remove it and still say:

"Msg 5042, Level 16, State 12, Line 1
The filegroup 'fg1' cannot be removed because it is not empty."

I also try to follow Bimal's steps, but it can't work.

Thanks all the same!
Post #682080
Posted Tuesday, March 24, 2009 4:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 PM
Points: 135, Visits: 87
I think I had the same issue. I was able to remove all the file groups using the method i explained but one.

Ty this query. Replace the FileGroup0 with your filegroup name.

select distinct so.name
from sys.objects so
inner join sys.indexes si
on so.object_ID = si.object_ID
inner join sys.data_spaces ds
on si.data_space_id = ds.data_space_id
inner join sys.filegroups fg
on ds.data_space_id = fg.data_space_id
where fg.name in ('FileGroup0')
In my case i came up with following results.
queue_messages_1395848385
queue_messages_1427848499
queue_messages_1459848613

Let me know if you face the same situation. I am still investigating a way to remove these objects which belongs to this file group even when there are no files present. I will let you know if I managet to remove them and the filegroup.
Post #682948
Posted Tuesday, March 24, 2009 7:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 11:29 PM
Points: 96, Visits: 132
Yea, i used my filegroup name instead of 'FileGroup0' and tried the query. But the results of mine are different from yours, i got nothing.

Look forward to your investigation~
thanks~
Post #682997
Posted Tuesday, March 24, 2009 7:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 PM
Points: 135, Visits: 87
do you have partitioned tables in the database?
Post #683000
Posted Tuesday, March 24, 2009 7:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 11:29 PM
Points: 96, Visits: 132
Haven't.
In fact, the database is only my test db. In a testing , I find this question by chance.
Post #683005
Posted Wednesday, December 1, 2010 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, Visits: 81
Thank you -- worked awesome for me!
Post #1028784
Posted Wednesday, December 29, 2010 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:31 PM
Points: 5, Visits: 249
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?
Post #1040546
Posted Wednesday, December 29, 2010 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, Visits: 81
what is the error you are getting?
Post #1040558
Posted Wednesday, December 29, 2010 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, Visits: 81
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
Post #1040560
Posted Wednesday, December 29, 2010 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:31 PM
Points: 5, Visits: 249
Feeling pretty stupid.

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. :) )
Post #1040562
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse