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 reallly require. Back in 2015 Fenwick Software 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 Software 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.

mm
Written By Jesse Clarnette

Jesse moved from Ballarat to Melbourne in 2009. He joined Fenwick Software in early 2016 while finishing his Masters of Business Information Technology at RMIT University in Melbourne. While studying Jesse worked on mobile applications for a not for profit organisation dealing with alternative currencies. He has also worked in the service industry including a stint at Cumulus Inc., one of Melbourne's best known restaurants.

We're here to help

Contact

Systems that Work

We implement tailored solutions to improve productivity, efficiency and deliver business benefits. Our clients are pleasantly surprised at how much difference sound advice, the right system and appropriate training can make.

Success stories are examples of how we’ve solved problems, brought about improvements, and delivered business benefits across a variety of businesses in Australia.

Thanks!

We're here to help!

Call us

03 9695 3333

Or, leave us a message