Each day as we process our normal transactions in 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 Fenwick released Data Maintenance tools with our standard Fenwick Gold Foundation FG00 to allow everyone to manage their database size in a user-friendly environment.
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 NAV excessive database growth could be caused by an interface that’s been running for years and holds onto log records, or Change Log.
Change Log
Change Log is a great tool to monitor activities that happen across the system to ensure that trace-ability is maintained. The tables we choose to monitor can have a great impact on how quickly this log grows. For Change Log we should think of table in NAV as having a few different types:
Transactional tables:
- Regularly have new records created, deleted, and modified
- Examples include Sales Header and Sales Line
Primary data tables:
- Hold key data which is used in transactional tables
- Examples include Vendor, Customer and Item
Choosing to monitor all fields on a transactional table will immediately start growing the Change Log, as each time a sales order is created every field will have a log record created. The same applies for modification and deletion.
Primary data tables however are much safer to monitor all transactions against as they do not get created, modified or deleted as often.
Data Maintenance Tools
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 NAV database, “Table Information”. Here we can analyse these tables and, in some cases where the records are no longer required, such as Change Logs. We can specify that after 200 days we would like all Change Logs 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 Interface Controller FG28 to keep the overall data it stores to a minimum. This has proven very useful as interfaces can commonly create thousands of records every day.
Archiving
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 NAV uses day-to-day.
Narada Ellis’ Blog Post covers some of the many ways we can tackle issues you might be having with performance and data in NAV.
