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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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: Friday, July 11, 2014 4:43 AM
Points: 78, Visits: 94
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:

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
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: Friday, July 11, 2014 2:59 PM
Points: 46, Visits: 188
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
Post #1546488
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse