Tables in Dynamics AX 2012 are divided into different categories: Main tables such as Customers, Ledger Accounts, Vendors, Items, etc, Transaction headers such as Sales headers, Purchase order headers, etc, and transaction details such as Sales lines, PO lines, etc. These tables are all related to each other through keys. For example when a sales order is created for customer A123, the customer, sales header and sales line are all linked to each other.
Occasionally one or more of these transaction records can be abandoned, meaning the parent data was deleted but the transaction still exists. We call these orphans because they exist in the database but are not connected to any other record. While these orphans by themselves cause no problem, they take up space in the database and given enough time, a lot of these orphaned records can cause performance issues.
Dynamics AX 2012 has a tool to spot and remove these orphans. It’s called the Consistency Check tool. It basically runs through the entire database and locates transaction records that do not have any parent record associated with them. The Consistency check tool is located under System Administration > Periodic > Database > Consistency Check
When running this tool, you will first be presented with a series of options such as which modules you would like to check for orphans in, what to do with orphans found (check or fix) and a starting date to perform the search on (if you run this tool every 90 days you can select a day just 90 days back in order to speed up the search process).
The tool will take an hour or more to run, be sure to run this after hours.
AX does a great job of keeping track of transaction data and removing automatically when any parent data is removed, but, as with any ERP, different scenarios may come up in which records are left orphaned. Using the Consistency Check tool on a regular basis is a great way to clear these records and keep your system running smoothly.