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
Posted Wednesday, December 29, 2010 1:10 PM


Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, Visits: 81
glad I could help!
Post #1040565
Posted Monday, July 11, 2011 10:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 11, 2015 2:38 AM
Points: 78, Visits: 96
Grasshopper you are a star! I have had the same problem on our production DB for weeks. Empty file, delete file, empty filegroup, "The filegroup 'UserData' cannot be removed because it is not empty.
Thanks to your script I have found a table that claims to have a PK on the old filegroup. Moving that to a new filegroup had no effect. Dropping the PK had no effect. Then (by adding ,* to the end of your script) I noticed that there was still a NULL entry for the table with "LOB_DATA" in the type_desc column.

There was indeed a column defined as nvarchar(max) and checking the maximum datalength in that column for the table, it showed me 2338 as the maximum used, so I changed the column to nvarchar(2400) instead, and without any other changes I was finally able to drop the empty filegroup.

SSC-Journeyman, I hope this helps you too:

TableName = OBJECT_NAME(p.object_id),
PartitionNo = p.partition_number,
FileGroup = FILEGROUP_NAME(a.data_space_id)
,IndexName =
, *
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
ORDER BY FileGroup, TableName, p.partition_number

Now that just leaves me one more tricky filegroup to sort out - does anyone know why my empty PRIMARY filegroup is still taking up 18GB of disk space and will not shrink any further?!?

Post #1139872
Posted Thursday, February 20, 2014 11:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:29 AM
Points: 3, Visits: 15
with ur work around same problem exists

can any please tell how to remove filegroup from DB.

Before removing i am taking backup of database then removing filegroup for making that production table less heavy .

Also i will be putting restoring that backup if any problem in the database

i am creating a archival engine where there is automatic backup and restoring when needed
Post #1543834
Posted Friday, February 28, 2014 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 47, Visits: 217
I believe you have to take the following steps first

1. Drop all the tables first
2. Remove file/files from the filegroup


Post #1546488
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse