decodeDynamics

When upgrading from a legacy Dynamics version to the latest one, it is often observed that a key concern for stakeholders is the size of Production data and the time required to migrate it to the latest version. This upgrade process presents a perfect opportunity to analyze your production data and implement a cleanup strategy. For table-level cleanup, you can begin by examining the data growth over time. To do this, log in to the SQL server using a SQL client, navigate to the Reports section, and click on ‘Top tables by data size’.

This provides a high-level overview of your organization’s data, allowing you to quickly identify tables with significant data growth. Typically, among the top 10 tables, you will find that Audit, Principal Object Access, and Async Operation tables are the most prevalent ones.

• Lets understand each of these table’s significance and cleanup approach.
  • Principal Object Access Table: Stores data related to record sharing & assigning, POA table is managed by the system to ensure proper access of users or teams to respective records. Direct deletion on this table is not supported, since it can break the security model defined for the organization. The correct way to clean up the POA table is by adjusting the security model, and revoking access that was previously given. For on-premise, from my personal experience, I did cleanup by running script provided by Microsoft for POA cleanup, basically it cleans the orphan records in the POA table.
  • Design consideration to control POA growth
    • Set whether reassigned records are shared with the original owner where needed. Note that this is a system-wide setting. Once it’s turned on, the setting is applied to all records
    • Share with users for collaboration where the list of users isn’t the same in the different records you shared.
    • Use the team as the record owner if you frequently share records with the same list of users or share the record with the team.
    • If you have a complex business unit structure and frequent use of sharing:
    • Share only where needed.
    • Minimize the number of business units.
    • Make sure that users are placed in the appropriate business unit.
    • Share to the team to allow users from different business units to access records.
    • When a record can be accessed by multiple access teams—for example, a sales representative team that can only read the record, and another sales manager team that has full read and write access—consider using different role-based forms for sales representative and sales manager.
    • Manage the lifecycle of your access team members. Remove users who are no longer needed for the collaboration.
    • Remove all access team members when collaboration is over. .

• Audit Table : Keeps data for record changes audit and user access logs
  • Cleanup: You can discuss with the stakeholders regarding the retention of Audit data and establish a policy for effective Audit management. Analyze if any unnecessary Audit data is lingering in the system, and if identified, you can navigate to
    • Settings > Audit and logs > Audit Log Management to delete such data
    • Select the Log file which you need to delete and click on Delete.
  • Design consideration to control Audit table growth
    • Carefully design Audit Management by discussing with stakeholders and enabling auditing only when necessary.
    • Choose specific fields that require auditing to reduce unnecessary data accumulation.
    • Implement a clear audit data retention policy, consider performance impact, and establish regular reviews for system efficiency.
• Async Operation Table : AsyncOperationBase table is used to store system jobs. System jobs represent asynchronous extensions, such as asynchronous registered workflows and plug-ins, and other background operations such as bulk deletion, bulk import, and rollup operations.
• Whenever an asynchronous workflow is triggered within Dataverse organization, it initiates the creation of a record in the AsyncOperationBase table. This record serves the purpose of tracking the progress of the async job. Additionally, the WorkflowLogBase table generates supplementary records to preserve comprehensive logs related to the workflow execution process
  • Cleanup
    • You have the ability to create bulk delete jobs for removing table data by setting specific filter criteria, such as:
    • “System Job Type” Equals “Workflow” to target workflow records.
    • “Status” Equals “Completed” to ensure only completed workflows are eligible for deletion.
    • [Optional] Filter on the “StatusCodes” (succeeded/failed/canceled) that are valid for completed “StateCode.”
    • [Optional] Filter on the “Completed On” field to delete only older workflows.
    • [Optional] Apply any additional filters as needed.
  • Design consideration to control Async Operation growth
    • You can choose the option “Automatically delete completed workflow jobs (to save disk space) in the workflow editor”
    • Enable the “Keep logs for workflow jobs that encountered errors” option within the workflow editor for synchronous workflows. By doing so, logs from workflow executions that encounter errors will be retained, providing valuable information for debugging and resolving issues. On the other hand, logs from successful workflow executions will be automatically deleted to conserve storage space. This approach strikes a balance between preserving essential data for problem-solving and optimizing storage efficiency.
• Additionally, you can engage stakeholders in discussions to explore other top tables and identify opportunities for cleanup. For example, consider customers who have not interacted for years, closed activities, or cases that have been closed for an extended period.
• You may decide to move these records to a other less expensive storage, making them easily retrievable when needed. This approach optimizes data storage while ensuring relevant information is readily accessible as and when required

You can also look Upgrade guide for exploring end to end upgrade process, and FAQs.