ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Sql Statement to retrieve maintenance information of UC4 System Tables.


Article ID: 88460


Updated On:


CA Automic Workload Automation - Automation Engine


Running the following SOL Statement against a Automic Database returns maintenance information of the Automic System (MQ) Tables:

The information returned by this statement can help investigate the root cause of performance issues:

It delivers the Total Space, Data Space, Index Space and Unused Space of the MQ tables.

TableName, NumRows,
 reservedpages *8192/1024 as TotalSpace,
 pages * 8192/1024 as DataSpace,
 (usedpages-pages)*8192/1024 as IndexSpace,
 (reservedpages-usedpages)*8192/1024 as UnusedSpace

t.[name] as tablename,
avg([rows]) as NumRows,
sum(total_pages) as reservedpages,
sum(used_pages) as usedpages,
When it.internal_type IN (202,204) Then 0
When a.type < 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) as pages
from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
JOIN sys.tables as t on p.object_id=t.object_id WHERE like 'MQ%'
group by t.[name]) as subselect;



Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager


The Information returned by the Database will look like this:

<Please see attached file for image>

align="bottom" class="documentation" border="0" alt=".png" src="" width="471" height="293">


!Note: A large unused space on the MQ tables can lead to performance issues.