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.