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.
You might have heard users complaining about the slow AX. For me, this is one of the most frequently encountered post implementation scenario. So, is Microsoft Dynamics a poorly designed application which ultimately ended up with a slow performance? In my opinion, the answer is no. Microsoft Dynamics AX has been designed considering the scalability as one of the core application design principle.
So, why actually AX becomes slow with the passage of time? There can be number of reasons behind it which can be related to hardware, software components or poorly designed customizations. Today, I am going to summarize the high-level things which you must check before further going into further detail analysis:
- Does your environment run periodic maintenance SQL Server and AX jobs? Periodic maintenance jobs can include index defragmentation, SQL Server database statistics update, database log clean up, inventory dimensions clean up etc. These are very important maintenance procedures and definitely have a positive impact on the overall application performance.
- Is your hardware sizing being appropriate? Use the Windows Performance Counters to sample the data for disk IO, network interface and processors to identify any hardware related issues.
- Is your tempDB sized properly? tempDB configuration plays a vital role in achieving the best possible performance out of AX. There are specific guidelines for configuring the tempDB for Microsoft Dynamics AX.
- Is your AOS, AX client and SQL Server databases are configured in accordance to the Microsoft suggested best practices?
- If you are using third-party Microsoft Dynamics AX add-ons or your AX is heavily customized then you must monitor for slow running database queries to identify missing indexes. You can use Microsoft DynamicPerf tool to identify the slow running queries and missing indexes.
AXPulse Consulting offers a specialized service named “Application Health Check”. As part of this service we perform the detailed analysis of your existing environment and prepare a report to identify the performance bottlenecks along with recommendations on how to resolve them.
If you are interested to know more about this service you can contact us at email@example.com
AXPulse team recently worked on integrating Act-On, a marketing automation platform with Microsoft Dynamics CRM On-Premises for one of our customers in North America.
With this bi-directional, real time native integration, the customer in Professional Education sector is now able to get the prospect data from their website to Act-On and then to the CRM system as converted leads. Sales teams accesses CRM to get real-time information, including prioritized leads and activity history for leads and contacts.
We also developed a round robin lead distribution solution in CRM that assigns the leads (one-by-one) to respective team members of the team(s). Appropriate filters were also applied to segment the data based on customer specific criteria. A notification was set for the team lead about the newly generated lead record and to whom it is assigned.
When trying to apply a payment to an invoice in Dynamics 365 for Finance and Operations a person with a cross icon is displayed on the Settle transactions form. Typically, you could click on Inquiry> Specifications to see the transaction it is marked against. If this is blank you are not able to unmark the transaction to mark it again correctly.
To resolve this issue, you need to can now run the Consistency Check (System Administration> Periodic Tasks> Database> Consistency check) and selecting the Payments option. This allows users to unlock these transactions for them to be marked again to be settled. This consistency check will identify and clean up the orphaned settlement (SpecTrans) records.
Recently Microsoft Dynamics AX Support team has published the DIXF Performance Benchmark for Microsoft Dynamics AX 2012. It is a good blog post to setup the right expectations from the DIXF framework. For benchmark results you can visit this blog post.