I came across this issue for couple of customers. The database size grows with the passage of time and this is quite normal thing for a transactional system like Microsoft Dynamics AX. At the same there are some recommendations from Microsoft, if followed properly will avoid growing database unnecessarily.
Today, I am going to discuss how to monitor the database growth and Microsoft recommendations to fix them. I will be covering only the SQL Server data file and not the log file here. The first starting point is going to find out which are the largest tables in your AX DB who is using the major chunk of your disk space. There is a default report exists in SQL Server to list all the top tables by size. You can access this report by right clicking on your AX Db and then follow the following path:
Do check the report if the report outcome shows tables like: BatchJobHistory, EventCUD, SMMTransLog, InventDim, BOMCalcTrans etc. as large tables. For one of my customer the size of BatchJobHistory has grown up to 550 GB whereas the overall size of all the other tables was not more than 30 GB.
There exist some of the periodic clean up processes in Microsoft Dynamics to periodically clean this data. Some of the processes are:
1. SMMTransLog: This table belongs to the sales and marketing module. It keeps log of each and every change you are making in this module. If you want to clean up the entire table then Microsoft has not provided a periodic process to do this. You can truncate this table directly from the SQL Server.
2. BatchJobHistory: This table records the batch job execution log depending up on the configuration of the batch jobs. In AX 2012 you can clear this table from path:
System Administration ->Inquiries -> Batch jobs -> Batch job history, however, If you want to clear the entire log then perform the unconditional delete on this form.
3. EventCUD: This table is used if you are using the change based alerts. This table should not grow as by default the records gets deleted from this table as soon the event is processed through a batch job. In case if you have enabled the alert on most frequently change tables or fields then chances are that this table grow substantially. There is no periodic process to clean this table. You can truncate this table directly through the SQL Server.
4. InventSumTTSLog: This table is used by the MRP module. The data gets automatically deleted from this table provided you are using the MRP module and constantly updating your plans. The data in this table can grow substantially if you have the MRP configuration key enabled and you are not using the MRP module. If you are not using the MRP module then there are two ways to clean this table – disable the MRP configuration or truncate this table directly through the SQL Server.
5. InventDim: This table is used to store all the possible inventory dimensions in an AX company. There is a possibility that it contains combinations which are never used in the company. Microsoft has provided a periodic process to clear the invalid inventory dimension combinations. You can access this from path below:
Inventory and warehouse management> Periodic> Clean up> Inventory dimensions cleanup
There are various other clean up jobs available for different modules which can improve the performance of the system by reducing system response time, below are the following:
General ledger> Periodic> Clean up > Clean up ledger journals
Inventory and Warehouse Management:
Inventory and warehouse management> Periodic> Clean up> Inventory journals cleanup
Inventory and warehouse management> Periodic> Clean up> Inventory settlements cleanup
Inventory and warehouse management> Periodic> Clean up> Dimension inconsistency cleanup
Organization administration> Periodic> Calendar cleanup
Organization administration> Periodic> Delete inactivated addresses
Procurement and Sourcing:
Procurement and sourcing> Periodic> Clean up> Purchase update history cleanup
Production control> Periodic> Clean up> Production journals cleanup
Production control> Periodic> Clean up> Production orders cleanup
Sales and Marketing:
Sales and marketing> Periodic> Clean up> Sales update history cleanup
Database Shrink and Re-indexing operations are recommended after performing these cleanup tasks for the physical allocation of the space reduced after these cleaning operations.