Our DBAs made a check on the size of the Oracle table spaces associated to some AutomationEngine servers.COLD start will indeed perform a TRUNCATE on all MQ tables, however it's usually not recommended to resort to this solution. Please refer to this discussion for more details: https://community.automic.com/discussion/7218/the-cold-start-hot-topic
Most of the time, the size of the table related to the MQMEM table is quiet small (300 to 500MB).
But some servers have a huge one (14 and 25 GB).
How to know if this is really in use ? Is there a way to purge these tables (COLD restart ?) and if we do this what is the impact of the database size (does the COLD restart free the MQMEM tables space, and do we get the space back ?)
I would suggest doing this:
1 - Ensure that reorganization of the MQ tables is enabled on the affected Automation Engine. Look for 'MQ_CHECK_TIME' in UC_SYSTEM_SETTINGS and verify that it is not set to '0', in which case MQ tables are not automatically maintained by the AE.
2 - Eventually replace your current version of the UC_REORG procedure with the enhanced version available here : https://automic.force.com/support/apex/CommunityArticleDetail?id=ka3b0000000g05b
Please align with your DBA for this.
3 - The space used by tables needs to be reclaimed. This has to be done by the DBA as well. Here is an Oracle knowledge link that should give you guidance on how to proceed : https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#ADMIN01401