For some reason the indexes created by the service broker queues appear to be created on the default filegroup (and not PRIMARY where all the other system objects get created). We have moved everything else out of this filegroup (DATA_01), changed the default filegroup (DATA) and want to delete this old one (DATA_01), but we can not as the indexes created by the service broker are there and they can not be moved.
The code below shows which tables/indexes/objects are on each file group and all we have left are the indexes 'queue_secondary_index' and 'queue_clustered_index'. A create index with drop existing does not work.
Any idea how we can proceed?
pcd
selectobject_name( i.object_id ) as MyObject
, i.name as MyIndex
, fg.name as MyFilegroup
fromsys.indexes i
joinsys.filegroups fg
oni.data_space_id = fg.data_space_id
order by 3,1,2 desc