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 ««12

Tried to remove filegroup Expand / Collapse
Author
Message
Posted Thursday, March 1, 2012 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:19 AM
Points: 100, Visits: 563
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

1
2
3
4
6
9
10
11

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: Friday, February 28, 2014 11:52 AM
Points: 1, Visits: 49
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 fg.name = @file_group_name /** your file group name **/
Post #1448040
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse