Service Broker indexes on non primary filegroup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » Service Broker » Service Broker indexes on non primary...

Service Broker indexes on non primary filegroup

Rate Topic

Display Mode

Topic Options

Author

Message

pcd_au

Posted Wednesday, July 07, 2010 10:01 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:28 AM
Points: 43, Visits: 178

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

select	object_name( i.object_id ) as MyObject
	, i.name as MyIndex
	, fg.name 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 »

Permissions