Each day as we process our normal transactions in Dynamics 365 Business Central and Dynamics NAV we are slowly growing the size of the database. While little can be done to stop this, much can be done to ensure you are only storing the data that you really require.

Back in 2015 we released Data Maintenance tools as part of our standard Fenwick Gold Foundation solution to allow everyone to manage their database size in a user-friendly environment. This is now part of the IT Admin Toolkit as part of Business Central.

Managing your Business Central Storage Space

The price of digital storage space is declining, and given that Moore’s law has been true for roughly 50 years, we know that hardware improvements are always just around the corner. However, there are still things you need to do to make sure your database has clean data and is not bloated from unnecessary records being kept.

We’ve found at many clients that the change log is between 5% to 50% of their entire database. In Business Central, excessive database growth could be caused by an interface that’s been running for years and holds onto log records, or Change Log.

There isn’t always a direct correlation to a large database being less performant (given that regular performance tuning is applied) though it can cause performance issues if neglected for too long.

Data Maintenance tooling and management

The tools we’ve developed allow users, at one click of a button, to get a snapshot of the relational size of each table with the system’s database, “Table Information”. Here we can analyse these tables and, in some cases where the records are no longer required, such as Change Logs.

With our tools we can specify that, say, after 200 days, we would like all change log entries older than this time to be deleted. This allows you to keep records for a nominal period while keeping your database in good shape.

In many cases we’ve applied this to our own Gold product, i.e. Interface Controller, to keep the overall data it stores to a minimum. This has proven very useful as some business interfaces can commonly create thousands of records every day.

As of Dynamics 365 Business Central this is now included as part of the our IT Admin Toolkit solution. You can see a list of recommended tables to cleanup in our support article.

A deep dive into the Business Central Change Log storage

The Change Log is a great tool to monitor activities that happen across the system to ensure that traceability is maintained. The tables we choose to monitor can have a great impact on how quickly this log grows.

When setting up the Change Log, we can sort tables that are tracked into a few broad groups:

Document and transactional tables

  • Regularly have new records created, deleted, and modified.
  • Examples include the Sales Header, Sales Line, Item Ledger Entry tables.

Primary data and master data tables

  • Holds key data which is used in transactional tables.
  • Examples include the Vendor, Customer and Item tables.

If you choose to monitor “all fields” on a document or transactional table, your system will immediately start growing the Change Log quickly, as each time a Sales Order is created every field will have a log record created. The same applies for modification and deletion. We do not recommend monitoring these tables.

Primary data and master data tables however are much safer to monitor all transactions against as they do not get created, modified or deleted as often. This can help you track changes to your key data over time.

Archiving records

Fenwick has been working to extend its current functionality to allow each record to be archived instead of deleted. Archiving is a great option for records which you may need in years to come, but have no immediate use. This still allows us to free up the tables that are used day-to-day.

Narada Ellis’ article on Dynamics 365 Business Central and NAV performance covers some of the many ways we can tackle issues you might be having with performance and data in the system.