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, February 9, 2009 1:43 AM
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
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 ...;)
Post #652585
Posted Monday, February 9, 2009 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 6:08 AM
Points: 21, Visits: 153
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)
Post #652680
Posted Monday, February 9, 2009 5:39 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
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?
Post #653344
Posted Tuesday, February 10, 2009 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 6:08 AM
Points: 21, Visits: 153
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
Post #653666
Posted Wednesday, February 11, 2009 12:09 AM
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
Dear damian:

The sript you provided sounds like incomplete?
Would you please help to check it, thanks!
Post #654484
Posted Wednesday, February 11, 2009 12:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 6:08 AM
Points: 21, Visits: 153
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
Post #654488
Posted Wednesday, February 11, 2009 12:46 AM
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
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.
Post #654499
Posted Wednesday, February 11, 2009 1:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 6:08 AM
Points: 21, Visits: 153
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
Post #654514
Posted Wednesday, February 11, 2009 1:46 AM
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
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.
Post #654523
Posted Sunday, March 22, 2009 5:05 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
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.
Post #681162
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse