Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:59 PM
Points: 73, Visits: 243
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 »

Add to briefcase

Permissions Expand / Collapse