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 AS queue_name
,q.object_id AS queue_id
, AS internal_schema_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 these internal tables are used for:







EXTENDED_INDEXES (such as a spatial index)





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s