December 1, 2011 at 6:55 am
Hello,
Just been trying to remove an empty filegroup and SSMS is telling me that it is not empty.
I removed the two files present in the group using "ALTER" and all went well. SSMS now reports that there are zero files present in the filegroup when viewing the Filegroups section of the database properties page.
Simple question: why am I told that the filegroup is not empty when attempting to remove it?
December 1, 2011 at 7:03 am
Is it the primary file group? If so, it can't be removed regardless of whether it's empty or not.
December 1, 2011 at 7:10 am
Richard Moore-400646 (12/1/2011)
Is it the primary file group? If so, it can't be removed regardless of whether it's empty or not.
No, it's not the PRIMARY group.
December 1, 2011 at 7:32 am
If you moved tables from the file group by rebuilding the clustered index to another file group that will not move any non-clustered indexes on the table(s). You'll have to drop and re-create them on a different file group as well.
The probability of survival is inversely proportional to the angle of arrival.
December 1, 2011 at 7:44 am
you could try this to find out whats still on the filegroup
SELECT
o.[name] AS ObjectName,
o.[type] AS ObjectType,
s.[name] AS SchemaName,
f.[name] AS [Filename],
i.[name] AS PrimaryKey,
i.[index_id] AS IndexID
FROM
sys.indexes i
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
INNER JOIN
sys.all_objects o
ON
i.[object_id] = o.[object_id]
INNER JOIN
sys.schemas s
ON
s.[schema_id] = o.[schema_id]
order by
s.[name],
o.[name]
December 1, 2011 at 7:54 am
sturner (12/1/2011)
If you moved tables from the file group by rebuilding the clustered index to another file group that will not move any non-clustered indexes on the table(s). You'll have to drop and re-create them on a different file group as well.
OK, as someone who is quite new to SQL, I'm not entirely sure I understood all of the above. 🙂
However, I can tell you that the physical files belonging to the filegroup in question have gone, so I'm not sure there's anything residual that I'm aware of associated with the filegroup. I don't think there are any tables that used that filegroup present either.
I don't believe I did any clustered index rebuilds on to other filegroups on any table associated with the filegroup in question.
Is there a way you could suggest I find out if any of what you've suggested has or needs to be done?
Apologies for sounding like a moron, but I'm still stumbling my way through this new technology and I have a lot to learn.
December 1, 2011 at 9:36 am
Fantastic!!
That's just the sort of thing I was looking for - a query to identify what tables belong to what filegroups! 🙂
This query has allowed me to see that I do still have a table associated with the filegroup in question, which, of course, explains why I am unable to delete the filegroup.
Thank you so much for your help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply