Database Clean Up

Navision · Shopware · App

Print Friendly, PDF & Email

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!

Suchen Sie eigentlich nach einer Möglichkeit, Benutzer in Navision / Business Central zu löschen um sich z.B. Zugang auf eine Datenbanksicherung zu verschaffen?

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 einfach nur STRG+Ende, und ich stehe auf dem neuesten Beleg. Nicht so, wenn man die Belegnummern nach Jahren formatiert (LS99.12345), und dann z.B. in 1999 angefangen hat. Dann versperren Belege, welche mit 99… anfangen, den Blick auf die aktuellen Belege (20…21…22…)! Unterschätzen Sie den dabei anfallenden Suchaufwand (Zeitaufwand) Ihrer Anwender nicht! Dies kann die allgemeine Navision-Performance (Arbeitsgeschwindigkeit) noch wesentlich mehr verschlechtern als alle anderen Bremsen in Ihrem 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?

Ich unterstütze Sie beim Aufräumen/Verkleinern/Bereinigen Ihrer Navision-Datenbank i.d.R. mit folgenden Vorgängen:

  • Ausgeben von Deb/Kred/SK-SuSa & Lagerbestandslisten, für die spätere Kontrolle, ob nicht zu viel gelöscht wurde
  • 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.
  • Debitorenposten, Kreditorenposten, Sachposten älter als 11 Jahre werden komprimiert. Dafür wird, je nach Navision-Version, die Komprimierung korrigiert. Dies gilt auch für die Artikelposten, wenn Ihr Navision das noch zulässt. Ansonsten wird aber auch schon über die echte Löschung der Artikelposten und Wertposten bei nicht mehr vorhandenen Artikeln viel Platz gespart.
  • Nach den obigen Verdichtungen werden veraltete (lange nicht genutzte) Kunden/Lieferanten/Sachkonten/Artikel ebenfalls inkl. ihrer Posten aus der Datenbank entfernt. Bei Kunden/Lieferanten/Artikeln gehe ich dabei von 5 Jahren aus. Sachkonten können natürlich erst nach den 11 Jahren gelöscht werden, wenn also keine oder nur komprimierte Posten dazu vorhanden sind. Alle Zeiträume werden noch einmal mit Ihnen abgestimmt! Hierbei wird auch stichprobenartig überprüft, ob Ihre Gebuchten Belege danach noch ausgedruckt werden können. Schlechte Programmierung verhindert das überraschend oft. In diesem Fall ist abzustimmen ob der Belegdruck angepasst wird oder der Lösch-Horizont.
  • Je nach Version werden hierbei auch die Artikelausgleichsposten generell gelöscht (verschiedene Navisionversionen benötigen diese gar nicht – Business Central hingegen benötigt diese generell für existierende Artikel).
  • 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!
  • Beim ersten Komprimieren erfolgt noch ein (bei Bedarf mehrere) Testlauf, so dass Sie über das Ergebnis einmal drüber sehen können, bevor ich Ihre Echtdaten reduziere. Inkl. erneutem Druck und vergleich der eingangs erwähnten SuSa-Listen. Die nötigen Programme bleiben danach Bestandteil Ihrer Datenbank! Auf diese Weise kann ich dann zukünftig, wenn Sie das möchten, regelmäßig (jährlich) Ihre Datenbank erneut aufräumen.

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?

Im nativen Navision bis Version 2009R2 ganz einfach: neue Form (Page), Table 2000000028 Tabelleninformation / Table Information, Preview (Speichern nicht nötig). Filtern auf Größe (KB) > 1000000 (1Gb, mit kleineren Tabellen muss man sich in der Regel nicht beschäftigen).
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. Klären, was mit ungebuchten Artikelbuchblattzeilen passieren soll (-> Löschen).
  6. Adjust (delete horizon) & call of the (new) function "Delete old data".

Estimated reading time: 14 minutes