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