Database Clean Up

Navision · Shopware · App

The Navision database only knows one direction: Grow! This behavior should (must!) be stopped once in a while.

By the way ... You can also delete data in the very old blue DOS Navision 3.56!

Why delete data in Navision?

There are good reasons for cleaning / compressing / shrinking a Navision database. And also for deleting data that is no longer required:

  • Search speed
    The fewer data sets Navision has to search, the faster searches or filters can produce a result. In the individual process at Navision, this often results in a barely noticeable delay or a tolerable few seconds per user. Overall, however, it slows the Business Central SQL Server down and delays the work flow for the whole company and for all employees again and again, noticeably or imperceptibly.
    It gets really annoying when you search for
    - the latest delivery note from Navision
    - the most recent order from Business Central
    - the last invoice from Business Solutions NAV
    --> Actually simply press CTRL + End and I'll be on the latest receipt. Not so if you format the document numbers according to years (LS99.12345), and then started e.g. in 1999. Then receipts that start with 99 ... block the view of the current receipts! Do not underestimate the search effort (time required) of your users! This can worsen the general Navision performance (working speed) even more than all other brakes in your SQL server!
  • Workflow
    Hundreds or even thousands of Navision documents (delivery notes, invoices, offers) that are no longer required or that are no longer required, and Business Central master data that are never required again, such as customers (debtors), suppliers (creditors), articles and G / L accounts, prevent the flow of work. Is Müller Schulze or Müller Schultze the right customer now? Oh, you picked the wrong one again ... And here, too, the performance of your employees suffers a lot more than short, often unnoticeable waiting times for the Navision SQL database.
  • Administrative time
    When reorganizing native Navision databases, optimizing keys in Business Central SQL databases, backing up data, restoring data, creating test environments, replicating database changes in high-availability systems: Gigabyte-by-gigabytes of unnecessary data are repeatedly overwhelmed by what is usually already chronically overloaded Network cables squashed. This also has an enormous impact on the performance of your data processing. Tape backups take longer and longer, hard drive backups take longer and more capacity. An unnecessarily large Navision (SQL) database or Business Central SQL database is also noticeable in pennies or Euros.
  • GDPR
    This point is practically always forgotten. The GDPR is a real minefield! Your statutory archiving obligation for the tax authorities (GDPdU) ends at the end of the 10th year after the last tax-relevant posting. If, for example, an invoice from December 31, 2010 is paid with a discount on January 2, 2011, the obligation to archive the payment expires on December 31, 2021. This means that the archiving obligation for the invoice and thus for the associated personal data also expires. You simply no longer have the right to save the personal data from this process! Either you remove the personal data from this receipt (invoice recipient, IP address ...) and all related receipts (delivery notes, shipping labels, goods statistics, seller invoices ...), or you simply delete all receipts at the latest after the archiving obligation has ended. I recommend deleting it completely because it also has the other advantages listed here.
  • Fiscal
    In fact, practically nobody knows: For the GDPdU (basic principles to access data and to check digital papers) you have to provide / output all data available in your Navision or Business Central 365 on request! Also, for example, invoice data for documents / processes in Business Central that are significantly older than 7 years for delivery notes or 10 years for invoices and other sales! Exception: You no longer have this data in stock. For this reason alone, you should observe a certain data hygiene in your Navision database.

Why not just simply delete data?

Unfortunately, in Navision / Business Central you cannot simply delete such a large amount of unnecessary data. There are ready-made routines for completed orders ("Delete completed orders"), the same for purchase orders ("Delete completed orders"). You can delete Navision offers and Business Central purchase inquiries directly via the tables very easily and quickly: Table 36 or 38: Run, set filter, mark all & with CTRL + a, delete. In the case of accounts receivable and articles, the associated item tables (21 accounts receivable items / customer ledger entry, 25 accounts payable items / vendor ledger entry, 32 item items / item ledger entry, 5802 value items / value entry) have unfortunately not been deleted for a long time. This is justified with the statistical continuity.

Also like to overlook: Table 405 Change Log Entry. Even small errors when setting up the change log are enough to literally flood the Navision / Business Central database with senseless data. In the course of my Navision programmer and consultancy days I have discovered a lot of systems in which the changelog has occupied 80% of a huge database - without any further use!

And cannot be influenced at all by “normal” users: 339 item adjustment items / Item Application Entry. In Navision versions before 2009 you don't need this table at all, you can completely remove its content with practically no consequences.

Depending on the Navision / Business Central version, some entries have to be deleted, but the other entries have to be compressed (compressed) in order not to damage (keep consistent) the G / L account balances or article inventories, for example.

How should you then clean up a Navision database?

I support you with tidying up / reducing / cleaning up your Navision database with the following processes:

  • Change of the program logic in your Navision or Business Central, so that item tables are really dismantled / removed / deleted during the following cleanups.
  • Dependent tables such as 379 Detailed customer items / Detailed Cust. Ledg. Entry and 380 Detailed Vendor Ledg. Entry with adjusted.
  • Offers older than 3 years, completed orders will be deleted. My routines go further than the standard Navision routines and recognize more jobs that have been completed. The same thing also happens in purchasing. Rule of thumb: Orders older than 2 years should no longer be delivered, they would rather cause irritation or laughter on the customer side ...
  • Purchase and sales delivery notes older than 7 years are automatically deleted. It is irrelevant whether they have already been printed or not. This means that you can finally find the latest Navision delivery note again immediately with CTRL + End without searching - if you have worked with number series such as L99xxxx, which previously prevented this.
  • Invoices older than 11 years will be deleted (you have an archiving obligation / GDPdU obligation for fiscally relevant movements for 10 years after the end of the posting period! This results in the 11 years). With this you can finally find the last / current posted invoice again immediately with CTRL + End - e.g. if you have worked with number series such as R99-xxxx, which previously prevented this.
  • Customer entries, accounts payable entries, general ledger entries older than 11 years are compressed. For this, the compression is corrected, depending on the Navision version. This also applies to the item entries, if your Navision still allows it. Otherwise, a lot of space is saved by actually deleting the item items and value items for items that are no longer relevant.
  • After the above consolidation, outdated (long unused) customers / suppliers / G / L accounts / articles including their items are removed from the database. For customers / suppliers / articles I assume 5 years. G / L accounts can of course only be deleted after the 11 years, if no or only compressed items are available for them.
  • When using the Microsoft SQL Server, the TransactionLOG is also checked. Unfortunately, there are still many system houses and / or hobby programmers who "back up" a Navision database (or more generally: an SQL database) by backing up the entire server, e.g. with Microsoft Backup or Veeam. Or back up the entire virtualized SQL server as a whole or - super intelligently - "incrementally". This is not a data backup for an MS-SQL database that is authorized or even recommended by Microsoft. No matter whether for Navision, Business Central or any other application on the SQL Server! If you then also set the recovery model to full (which is highly recommended for many reasons, e.g. for every 5 minute backups in high availability environments), the transaction log will fill up. No wonder: it is never emptied in a controlled manner! My personal record was a Navision database with 7 Gb user data (unadjusted) and 67 Gb transaction log. Then maybe this is no longer negligent, but maybe intentional!
  • When compressing for the first time, there is another test run (if necessary, several) so that you can see the result before I reduce your real data. The necessary programs then remain part of your database! In this way, if you want, I can clean up your database again regularly (annually) in the future.

What if that doesn't work for me?

In short: it usually fits, and (really) unnecessary fears are often in place. But of course every period, every master record, every deletion in Navision or Business Central 365 can be individually adapted to your needs.

How do I find out the biggest wastes of space?

In the native Navision up to version 2009R2 very simple: new form (page), table 2000000028 table information, preview (no need to save). Filter for size (KB)> 1000000 (1Gb, you don't usually have to deal with smaller tables).
Under MS-SQL (Navision and Business Central with the SQL Server) with this little script to query the table size:

USE Name-of-the-Navision-database
GO
SELECT TOP 50 
  used AS "Pages",
  rows AS "Saetze",
  (used * 8) / 1024 AS "MB",
  CAST(OBJECT_NAME(id) AS CHAR(100)) AS TableName
  FROM sysindexes WHERE indid IN(1,2,255) ORDER BY used DESC

The change log items are typically being displayed very high up here. Rule of thumb: Change log items should only be used on master data. E.g. on article master data (table 27), customer master (table 21), terms of payment, terms of delivery, etc.
The classic question that the change log should and can answer is like: "Who changed the number at Müller?" Or "Who changed the retail price for item 4711 from 6.90 to 5.80?". Standard Navision would only save "Changed on" and "Changed by" in a few different master data tables.
If you use the change log items incorrectly, e.g. to log transaction data, the change log will quickly burst at the seams, and with it your database.

The size of the transaction log can be found in the MSSMS under the properties of the respective database. There is no transaction log if you are working with the native Navision database up to version 20090R2. Rule of thumb: Transaction logs with a size over 1 Gb or a size> 2% of the user database are a sign of botch. This is not always true, e.g. the transaction log may have been inflated once during a major campaign and was then not reduced later. But this rule almost always fits. This is where the “used” or “used” information helps. In Navision up to 2009R2 you can also find the database sizes for the SQL Server under File / Database / Change.

Screenshot Navision/Business Central SQL Datenbank Datenbankgröße und Transactionlog Größe
Query Navision / Business Central SQL database database size and transaction log size
Screenshot vom Aufruf der SQL_Datenbank Informationen unter Navision 2009R2
Selecting the SQL database information under Navision 2009R2

Anzeige der Datenbankgröße einer MS-SQL Datenbank unter Navision 2009R2
Display database size of an MS-SQL database under Navision 2009R2
Screenshot Transactionlog einer MS-SQL Datenbank unter Navision 2009R2
Display of the transaction log size of an MS-SQL database under Navision 2009R2

Shrink the database files

After the deletion, there is often more than 50% "air" in the database files.

With the native database (Classic Client), no more than 15-20% empty space should be active. The smaller the database, the faster and more compact the HotCopy data backups are (this makes no difference with the logical one). The empty space should of course not be TOO little, since the database will grow again.

With the SQL database, Navision or the SQL database server can itself expand the Navision database files or the transaction log files if necessary. Therefore, these can be reduced to the lower limit. Here, too, the reduction in size ensures faster and more compact data backups. Defragmenting the indexes (Index Defrag) is part of every database downsizing! But in a clean Navision database you already have periodic processes for rebuilding the indices (index update) and the associated index defrag (defragmentation of the indices).

Please never activate the option “Automatically reduce size”! This option is absolutely counterproductive! With this option, your database is very heavily fragmented - it is literally "tattered" (shredded). The indices (keys) can be defragmented to such an extent that the SQL server does not need to use them!

If the database is on magnetic hard drives (I have only been recommending SSDs since 2008), the effective data access of Navision or Business Central also accelerates with smaller (scaled-down) database files, since the hard drive heads now have to cover shorter distances. Don't forget "Defrag" 🙂. Whereby more sins of setup (Raid5, several database parts on one disk, transaction log not on a separate disk ...) cause a slowed down Dynamics NAV / Navision / Business Central with a miserable performance. Practically no Navision programmer (or Business Central administrator or even any other database guru) understood why the new, ultra-modern 15,000 rpm hard drive with 120 Gbytes was introduced when new, faster and larger magnetic hard drives were introduced was soooo much slower than the 3 ancient 2 Gb HD's with 5,400 rpm…. With the native database server under Navision, 5 or 6 ancient HDDs could still be orders of magnitude faster than fewer / an ultra-modern hard drive. That was related to the table structure. But basically this behavior applies to every database process (keyword I/O Performance).

How often should you shrink the database?

Here it is not said: "A lot helps a lot". Annual downsizing and defragmentation as part of data hygiene is usually sufficient. Exception: Real bullshit has been made in your database, e.g. in connection with the changelog (change log items) or the transaction log. Then the downsizing (shrinking) and the index defragmentation belong to the repair tasks.

Data Clean Up Process

Sepcifically, the process is as follows:

  1. Change the program logic so that when master data is deleted, the associated items are also deleted (and not just on the account number "empty").
  2. Print a SuSa SK, Deb, Kred, print stock assessment.
  3. Get compression for general ledger entries (98), customer ledger entries (198), vendor ledger entries (398) and, if still possible, the item ledger.
  4. Let inventory adjustment run (alternatively: adapt code so that unregulated items are ignored).
  5. Clarify what should happen to unposted article book sheet lines (-> Delete).
  6. Adjust (delete horizon) & call of the (new) function "Delete old data".