Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove a Filegroup


Remove a Filegroup

Author
Message
amy.walsh
amy.walsh
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 81
glad I could help!
Greenius
Greenius
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 98
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. :-D

SSC-Journeyman, I hope this helps you too:

SELECT
TableName = OBJECT_NAME(p.object_id),
PartitionNo = p.partition_number,
FileGroup = FILEGROUP_NAME(a.data_space_id)
,IndexName = i.name
, *
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?!?

Cheers,
Greenius
Parvez-1133439
Parvez-1133439
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Sponge-592981
Sponge-592981
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 223
I believe you have to take the following steps first

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

Thanks,

Sponge
tung858
tung858
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 348
I actually found an easy solution to this fix since I had the same issue.

I was trying to remove the filegroup [fg_LMeterDetail_13] but got the "cannot be removed because it is not empty" error. This filegroup's only sin was being associated with a partition scheme. This filegroup had no data.

So after finding there's no opposite to NEXT USED, I experimented and found that you can issue another NEXT USED but pointing it to the previous filegroup:

ALTER PARTITION SCHEME ps_LMeterDetail

NEXT USED [fg_LMeterDetail_12]

After that, I was able to issue the REMOVE FILEGROUP which then automatically removed it from the partition scheme

ALTER DATABASE [WACOE] REMOVE
FILEGROUP [fg_LMeterDetail_13]

Voila! It worked!

You're welcome everyone.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search