February 9, 2009 at 1:43 am
I want to remove a filegroup like this:
"ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName"
and before i do that T-SQL, I have removed all the files in the filegroup that planed to remove.
But when remove that filegroup,
It says:
Server: Msg 5042, Level 16, State 7, Line 1
The filegroup 'MyFileGroupName' cannot be removed because it is not empty.
Question: How can I tell what is on this filegroup.
I'm having trouble in finding it ...;)
February 9, 2009 at 4:18 am
With this script you can see the objects that belong to the filegroup.
use
GO
SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
February 9, 2009 at 5:39 pm
Thanks damain~:)
With your script I see the objects belong to their filegroups.
But,though there is NULL in the filegroup, I still cann't remove it, and also says:
Msg 5042, Level 16, State 12, Line 1
The filegroup 'filegroup_name' cannot be removed because it is not empty.
How can I remove it?
February 10, 2009 at 7:01 am
Try this script.
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
February 11, 2009 at 12:09 am
Dear damian:
The sript you provided sounds like incomplete?
Would you please help to check it, thanks!
February 11, 2009 at 12:19 am
Sorry,
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
February 11, 2009 at 12:46 am
Thank you for your reply in time.
I have executed the script, and the result tells all indexes only on the PRIMARY filegroup. This may mean the other filegroups are empty.
In fact, I think if there are objects on the filegroup planned to remove, maybe I cann't remove the datafiles belong to it.
But I still cann't remove it and receive;):
Msg 5042, Level 16, State 12, Line 1
The filegroup 'fg3' cannot be removed because it is not empty.
February 11, 2009 at 1:32 am
To delete a filegroup, you must first delete the file associated with the filegroup.
ALTER DATABASE [your database] REMOVE FILE [filename]
GO
ALTER DATABASE [your database] REMOVE FILEGROUP [filegroupname]
GO
February 11, 2009 at 1:46 am
Yes, at the very beginning, I have removed all the datafiles on the filegroup.
So I said there should be no objects in the filegroup except PRIMARY.
But still I can not remove it.
March 22, 2009 at 5:05 pm
This is a bug with SQL server. HEre is the workaround.
1. Add another file to the same file group
ALTER DATABASE DBNAME
ADD FILE
(
NAME = Data_File_Name,
FILENAME = [X:\SQLData\FileName.ndf],
SIZE = 1MB,
FILEGROWTH = 10%
)TO FILEGROUP [FileGroupName];
2. Empty the first file
DBCC SHRINKFILE(First_Data_File,EMPTYFILE )
3. Now you can Remove the original file and the new file you added and the file group
ALTER DATABASE DBNAME REMOVE FILE First_File;
ALTER DATABASE DBNAME REMOVE FILE 2nd_File;
ALTER DATABASE DBNAME REMOVE FILEGROUP FileGroupName;
Hope this helps.
March 23, 2009 at 11:48 pm
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!
March 24, 2009 at 4:28 pm
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.
March 24, 2009 at 7:03 pm
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~
March 24, 2009 at 7:10 pm
do you have partitioned tables in the database?
March 24, 2009 at 7:24 pm
Haven't.
In fact, the database is only my test db. In a testing , I find this question by chance.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy