Top Tips for Microsoft Dynamics AX Performance Optimization: Part I – Purging data from tables

by | June 23, 2017 | Dynamics AX

Which Microsoft Dynamics AX system logging tables should have a data retention policy?

After working with Microsoft Dynamics AX for over 10 years, you start to see patterns in issues that arise. These patterns have become things I look for when analyzing the health of client’s Dynamics AX systems. In this ‘Top Tips for Microsoft Dynamics AX Performance Optimization’ series, I will be covering some of the items that I look for when analyzing the health of a Microsoft Dynamics AX system. Since there is a lot of information out there related to SQL Server and Dynamics AX performance I wanted to start with a couple non-SQL Server topics.  

Recommended tables for purging

The first topic is related to tables that I often recommend the customer considers purging data from. The reason is that they are what I would consider log tables of events that happened at some point in time in the system. After a certain time frame, knowing for example, that a batch job completed successfully is not necessary information for you to have. In this case, depending on how many batch jobs you have, you can accumulate a lot of information about past batch jobs.  

The topic of purging data can be tricky. However, a few tables that contain system data are low risk/low impact to purge on a regular basis.  

Low risk/low impact tables are:

  1. AIFDOCUMENTLOG & AIFMESSAGELOG – these tables contain information about AIF/Services calls that have been received by the system.  This information is great to have if a service call has any issues.  However, after time you can accumulate thousands of records and take up unnecessary disk space.
  2. BATCHJOBHISTORY & BATCHJOB & BATCHHISTORY – these tables contain information about batch jobs that have run in the system.  Every time a batch job runs information about the start time, end time, status of the batch job, and infolog messages (if there were any) are stored.  
  3. SYSDATABASELOG – this contains records for instances in which a person accessed a table in which logging has been setup.  If you setup Dynamics AX to log if someone updates a customer record then when a user accesses the custtable and updates a record there will be an entry in the sysdatabaselog table.

For these tables you should determine the answer to the following questions:

  1. Do we need to purge data from theses tables?  To answer this I typically recommend evaluating if you are using these tables. For example, you may not even be using AIF/Services or have logging turned on for it.  If that is the case then you probably don’t need to purge data from the AIFDOCUMENTLOG and AIFMESSAGELOG tables.  Another consideration would be to download the Dynamics Performance Analyzer and run the INDEXES_BY_SIZE portion of the 4-Analyze_SQL_Indexes script.  This will return the top tables in your Dynamics AX implementation by size.  If these tables don’t show up in the list, it may not be worth the effort to purge data from these tables.
  2. Can we purge data from these tables?  For some customers, due to policies of the company or auditing requirements, you may not be able to purge data from these tables.
  3. How often should you purge data from these tables?  For each table, you would need to evaluate how long you need to keep data.  For any log table, I typically suggest you keep it for at least as long in between when someone might look at the data.  For example, if you are looking at batch history or services log daily you might think I can purge it after a couple of days.  Some pointers I would give are to think about holidays and how as the result of how some holidays fall it could be up to 5 days before you would be in to look at the logs.  Also sometimes it is nice to see patterns (especially with batch jobs) of things like how long or how often something happens.  For this reason, I like to have two to three months of data if possible.
  4. What method should we use to purge data in these tables?  This can be done in a variety of ways:
    1. Through a SQL script – Some people will create a SQL script and create a SQL job that is scheduled to run on a regular basis on the SQL server.  This has a low impact on your Dynamics AX system performance as its being run directly in SQL but has a risk of not ensuring business data integrity.  It would not be recommended to use this method for other tables within Dynamics AX.
    2. Through X++ code – Some companies write X++ code that is scheduled through a batch job to run on a regular basis which will go through and clean up these tables.  Utilizing the AOS to ensure data integrity is a benefit from this method but adds overhead to the AOS and may impact performance if performed during business hours.

Since these specific tables are system tables and have been vetted as having a low impact on data integrity complications, these methods can easily be implemented for cleanup.  If you are however looking at purging other tables than the ones listed, you should utilize a tool such as Intelligent Data Management Framework (IDMF) that utilizes the AOS.

Now that you know what system tables to consider for purging you should run the INDEXES_BY_SIZE portion of the 4-Analyze_SQL_Indexes script that comes in the Dynamics Performance Analyzer to see if these tables are in your top 100 list. You can get Dynamics Performance Analyzer from the following site:  If these tables are in your top 100, the next step is to start going through the questions above and determine your next steps.


Related Posts


Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events


01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV


06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!