• 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.