Create a data maintenance strategy for Dynamics 365 finance and operations data (part two)

A well-defined data maintenance strategy improves the quality and performance of your database and reduces storage costs. In part one of this series, we covered the roles and responsibilities of your data strategy team, tools for reviewing storage usage, and data management features in Dynamics 365 finance and operations apps that your strategy should include. We recommended that you start your planning by decommissioning unneeded sandbox environments in your tenant. In this post, we focus on creating a data retention strategy for tables as part of your overall storage maintenance strategy.

Create a data retention strategy for tables

After sandbox environments, tables have the greatest impact on total storage volume. Your data maintenance strategy should include a plan for how long to retain the data in specific tables, especially the largest ones—but don’t overlook smaller, easily manageable tables.

Review table storage by data category

In the Power Platform admin center capacity report for the production environment, drill down to the table details.

Identify the largest tables in your production environment. For each one, determine the members of your data strategy team who should be involved and an action based on the table’s data category. The following table provides an example analysis.

Data category and examples	Strategy	Team members
Log and temporary data with standard cleanup routines SALESPARMLINE, USERLOG, BATCHHISTORY, *STAGING	This category of data is temporary by design unless it’s affected by a customization or used in a report. Run standard cleanup after testing in a sandbox. Note: If reports are built on temporary data, consider revisiting this design decision.	• System admin • Customization partner or team if customized • BI and reporting team
Log and temporary data with retention settings DOCUHISTORY, SYSEMAILHISTORY	This data is temporary by design but has an automatically scheduled cleanup. Most automatic jobs have a retention setting. Review retention parameters and update after testing in a sandbox.	• System admin • Customization partner or team if customized
Log data used for auditing purposes SYSDATABASELOG	Establish which department uses the log data and discuss acceptable retention parameters and cleanup routines.	• System admin • Business users • Controllers and auditors
Workbook data with standard cleanup routines SALESLINE, LEDGERJOURNALTRANS	Data isn’t temporary by design, but is duplicated when posted as financial. Discuss with relevant department how long workbook data is required in the system, then consider cleanup or archiving data in closed periods.	• System admin • Business users related to the workbook module • BI and reporting team for operational and financial reports
Columns with tokens or large data formats CREDITCARDAUTHTRANS	Some features have in-application compression routines to reduce the size of data. Review the compression documentation and determine what data is suitable for compression.	• System admin • Business users
Financial data in closed periods GENERALJOURNALACCOUNTENTRY	Eventually you can remove even financial data from the system. Confirm with controlling team or auditors when data can be permanently purged or archived outside of Dynamics 365.	• System admin • Controllers and auditors • Financial business unit • BI and reporting team for financial reports
Log or workbook data in ISV or custom tables Should start with the ISV’s three-letter moniker	Discuss ISV or custom code tables with their developers.	• System admin • Customization partner or team • ISV • BI and reporting team, depending on the customization

Consider whether table data needs to be stored

For each large table, continue your analysis with the following considerations:

• Current business use: Is the data used at all? For instance, was database logging turned on by accident or for a test that’s been completed?
• Retention per environment: Evaluate how long data should be in Dynamics 365 per environment. For instance, your admin might use 30 days of batch history in the production environment to look for trends but would be content with 7 days in a sandbox.
• Data life cycle after Dynamics 365: Can the data be purged? Should it be archived or moved to long-term storage?

With the results of your analysis, your data strategy team can determine a retention strategy for each table.

Implement your data retention strategy

With your data retention strategy in place, you can start implementing the actions you decided on—running standard cleanups, updating retention settings, configuring archive functions, or reaching out to your ISV or customization partner.

Keep in mind that implementing an effective strategy takes time. You need to test the effect of each action in a sandbox environment and coordinate with multiple stakeholders.

As you implement your strategy, here are some best practices to follow:

• Delete or archive data only after all stakeholders have confirmed that it’s no longer required.
• Consider the impact of the data life cycle on customizations, integrations, and reports.
• Choose the date range or the amount of data to target in each cleanup or archive iteration based on the expected duration and performance of the cleanup or archiving routine, as determined by testing in a sandbox.

Need more help?

Creating a data maintenance strategy for Dynamics 365 finance and operations apps is a complex and ongoing task. It requires a thorough analysis and collaboration among different roles and departments. For help or guidance, contact your Microsoft representative for a Dynamics 365 finance and operations storage capacity assessment.

Learn more

Not yet a Dynamics 365 customer? Take a tour and start a free trial.