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

Service Broker indexes on non primary filegroup Expand / Collapse
Posted Wednesday, July 7, 2010 10:01 PM


Group: General Forum Members
Last Login: Tuesday, November 29, 2016 2:43 AM
Points: 142, Visits: 400
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?

select	object_name( i.object_id ) as MyObject
, as MyIndex
, as MyFilegroup
from sys.indexes i
join sys.filegroups fg
on i.data_space_id = fg.data_space_id
order by 3,1,2 desc

Post #948980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse