Msg 5042, Level 16, State 12, Line 6 The filegroup '*****' cannot be removed because it is not empty.

_{Monday, April 6, 2020 10:24 PM}

Hi,

I am trying to delete file and file groups from database as they are not required. I was able to delete the file, however while deleting the filegroup I get the below message. I tried deleting the filegroup from SSMS and there also I get the below message.

Msg 5042, Level 16, State 12, Line 6 The filegroup '*****' cannot be removed because it is not empty.

I already tried below commands so that I can delete the filegroup

Alter partition function FUNC_PARTITION merge range ('N')
ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED [OTHER-FILEGROUP-NAME]
ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED

Swapnil Ambre

_{Monday, April 6, 2020 10:25 PM}

I already tried below commands and they don't work. Still I get the error while deleting the filegroup.

Alter partition function FUNC_PARTITION merge range ('N')
ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED [OTHER-FILEGROUP-NAME]
ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED

Swapnil Ambre

_{Tuesday, April 7, 2020 4:16 AM}

Hi Swapnil,

The filegroup cannot be deleted unless it is empty.First delete all files from the file group.if possible, drop the partition scheme and partition function.

Or refer to following post:
https://www.sqlservercentral.com/forums/topic/tried-to-remove-filegroup

Best Regards,
Cris

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Tuesday, April 7, 2020 4:43 AM}

Thanks Cris,

I had only one file associated with the filegroup. So I deleted it before I tried deleting this filegroup.

I have other files and filegroups created as per months in the partition function and scheme. Are you saying that I have to delete all of them also.

Also let me know do i have to do something before dropping the partition scheme and partition function. I mean what about the data which is there in other filegroups. how should i take care of them.

Swapnil Ambre

_{Tuesday, April 7, 2020 5:36 AM}

Hi Swapnil,

i mean drop the partition function and scheme, because you cannot directly modify the partition scheme and remove a filegroup from it. You must delete the scheme and rebuild it.
but the partition scheme can only be deleted if there is no table or index that currently uses the partition scheme. 

Did you read the link mentioned in my last reply, it seems there is a answer for the questioner.

Please make any changes in a test environment.(from test to production)

Best Regards,

Cris

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Tuesday, April 7, 2020 5:55 AM}

Hi Swapnil,
In ALTER PARTITION FUNCTION, I see following description:

Drops a partition and merges any values that exist in the partition into a remaining partition. RANGE (boundary_value) must be an existing boundary value, into which the values from the dropped partition are merged. This argument removes the filegroup that originally held boundary_value from the partition scheme unless a remaining partition uses it, or marks it with the NEXT USED property.

try below commands , then try to remove the filegroup:

ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED [the filegroup you want to remove]
ALTER PARTITION SCHEME PARTITION-SCHEME-NAME NEXT USED

Alter partition function FUNC_PARTITION merge range ('N')

Best Regards,

Cris

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Monday, April 13, 2020 3:12 AM}

Hi Swapnil,

Is there any update on this case?
Please feel free to drop us a note if there is any update.
If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

Best regards,
Cris

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.