Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Tried to remove filegroup Expand / Collapse
Posted Thursday, March 1, 2012 2:35 PM


Group: General Forum Members
Last Login: Thursday, May 26, 2016 1:39 PM
Points: 100, Visits: 621
nmederich (1/11/2011)
I'm sharing the answer that i found in the hopes it helps someone else.

Why did this occur? I could be wrong but my theory is this:
I had been massaging the partitions quite a bit, adding some new splits to the partition schema then removing some partitions by merging as a result there was gaps in the list of data_space_id's like so


id's 5 and 7 and 8 had been deleted, id 11 was the filegroup i was wanting to delete but could not.

The resolution:
I added 4 new filegroups and then added these filegroups to partition scheme which created ids 5,7,9, and 12. This cause SQL Server to remove id 11 from sys.destination_data_spaces and then i was able to delete the filegroup with no problem.

I had exactly the same problem, talked to MS and they walked me through exact same solution.
Lesson learned.
Post #1260456
Posted Tuesday, April 30, 2013 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 1, Visits: 56
I had the same issue on 2005 sp4. You can check the following query.

I do have to mention the file group was default all files were removed. However there were primary keys and table objects for sysdiagarms existed in the file group.

select *
from sys.filegroups as fg
join sys.indexes as i
on fg.data_space_id = i.data_space_id
where = @file_group_name /** your file group name **/
Post #1448040
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse