August 25, 2011 at 1:26 pm
I've inherited the DBA position over a large database server (550+ databases, ranging from 5GB-200GB per database). The databases all follow the same file design and are broken into three file groups: PRIMARY, AUDIT, and INDEX. Each file group has it's own LUN (the log files are on a separate LUN as well).
It is uncertain as to whether all the developers have followed the design over the years and there may be objects created on the wrong file groups. For example AUDIT tables may be on the PRIMARY file group or INDEXES may be on the PRIMARY file group.
My question(s): Is there a way I can tell which objects are on the wrong file group? Our AUDIT tables have a naming convention of "A_xxxxxxxx" and our Indexes generally follow "IX_xxxxxxxxx".
I've been messing around with the following query to see what I can find (found the query on SSC):
SELECT
[FileGroup] = FILEGROUP_NAME(a.data_space_id),
TableName = OBJECT_NAME(p.object_id),
IndexName = i.name
FROM sys.allocation_units a
INNER JOIN sys.partitions p
ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
LEFT JOIN sys.indexes i
ON i.object_id = p.object_id AND i.index_id = p.index_id
ORDER BY FileGroup
It looks like their suspicions were correct as I right away I see many tables named A_xxxxx that are on the PRIMARY file group.
Bonus Question: Is there anything I can do to prevent certain objects from being created on certain file groups to help mitigate this issue from continuing to happen?
August 25, 2011 at 2:10 pm
I think you can use Policy based management to make sure that it does not happen again. I have never tried it.
-Roy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply