Remove a Filegroup

  • 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 ...;)

  • 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)

  • 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?

  • 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

  • Dear damian:

    The sript you provided sounds like incomplete?

    Would you please help to check it, thanks!

  • 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

  • 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.

  • 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

  • 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.

  • 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.

  • 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!

  • 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.

  • 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~

  • do you have partitioned tables in the database?

  • 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