Reoganizing SQL Server internal tables

How to reorganize the internal tables in SQL Server is not immediately obvious:

For example to see the internal tables used for Event Notification:

SELECT q.name AS queue_name
,q.object_id AS queue_id
,s.name AS internal_schema_name
,it.name AS internal_table_name
,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id
JOIN sys.schemas AS s ON it.schema_id = s.schema_id
WHERE it.internal_type_desc = ‘QUEUE_MESSAGES’;

queue_name    queue_id    internal_schema_name    internal_table_name    internal_table_id
QueryNotificationErrorsQueue    1003150619    sys    queue_messages_1003150619    1019150676
EventNotificationErrorsQueue    1035150733    sys    queue_messages_1035150733    1051150790
ServiceBrokerQueue    1067150847    sys    queue_messages_1067150847    1083150904

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

Msg 1088, Level 16, State 9, Line 11
Cannot find the object “sys.queue_messages_1003150619” because it does not exist or you do not have permissions.

Find we have to connect using the DAC – connect as a sysadmin user changing the servername to start with admin:

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

— Msg 1914, Level 16, State 3, Line 1
— Index cannot be created on object ‘sys.queue_messages_1003150619’ because the object is not a user table or view.

So how do we reorgnize this then?

DBCC DBREINDEX(‘sys.queue_messages_1003150619’)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

NOTE: We included the schema name ‘sys’ in the command!

As per https://msdn.microsoft.com/en-gb/library/ms187799.aspx these internal tables are used for:

QUEUE_MESSAGES

XML_INDEX_NODES

FULLTEXT_CATALOG_FREELIST

FULLTEXT_CATALOG_MAP

QUERY_NOTIFICATION

SERVICE_BROKER_MAP

EXTENDED_INDEXES (such as a spatial index)

FILESTREAM_TOMBSTONE

CHANGE_TRACKING

TRACKED_COMMITTED_TRANSACTIONS

Advertisement

---