Database Clean Up

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!

Are you actually looking for a way to delete users in Navision / Business Central to gain access to a database backup, for example?

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 just CTRL+End, and I'm on the latest voucher. Not so, if the document numbers are formatted by years (LS99.12345), and then e.g. started in 1999. Then documents starting with 99... block the view to the current documents (20...21...22...)! Don't underestimate the amount of searching (time) your users will have to do! 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,quotes) 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. 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
  Tatsächlich auch praktisch niemandem bekannt: Für die GDPdU müssen Sie -auf Anforderung- alle in Ihrem Navision oder Business Central 365 bereitstehenden Daten bereit stellen/ausgeben! Auch z.B. Rechnungsdaten zu Belegen/Vorgängen in Business Central, welche deutlich älter als 7 Jahre bei Lieferscheinen oder 10 Jahren bei Rechnungen und anderen Umsätzen sind! Ausnahme: Sie haben diese Daten nicht mehr vorrätig. Schon allein aus diesem Grund sollten Sie eine gewisse Datenhygiene in Ihrer Navision-Datenbank beachten.

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 clean up a Navision database?

I support you in cleaning up/downsizing/cleaning your Navision database with the following processes:

• Output Deb/Kred/SK-SuSa & stock lists, for later control if not too much has been deleted.
• 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 items, vendor items, G/L items older than 11 years are compressed. For this, depending on the Navision version, the compression will be corrected. This also applies to the article items, if your Navision still allows this. Otherwise, however, a lot of space is already saved via the real deletion of the article items and value items for articles that no longer exist.
• After the above summarizations, obsolete (long unused) customers/suppliers/property accounts/articles will also be - incl. its items 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. Gerade hier ist Ihre Mitarbeit sehr wichtig: Benutzen Sie virtuelle Artikel, Sachkonten oder Kunden? Z.B. als Dachgesellschaften, Notizsammler oder für Preisgruppen? Bei diesen Sätzen kann ich ohne ihre Hinweise nicht automatisiert feststellen, ob ich die löschen darf, weil uralt und keine Bewegungen drauf, oder ob diese wichtige Daten tragen, die nicht gelöscht werden dürfen.
• Depending on the version, the article adjustment items are also generally deleted here (various Navision versions do not require these at all - Business Central, however, generally requires them for existing articles).
• 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!
• During the first compression one (if necessary several) test run takes place, so that you can check the result once, before I reduce your real data. Incl. renewed printing and comparison of the SuSa lists mentioned at the beginning. The necessary programs remain part of your data base! This way I can in the future, if you want, regularly (annually) clean up your database again.

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.

Hierbei ist es ganz besonders wichtig ihre Besonderheiten zu beachten! Haben Sie statistische oder aus anderen gründen relevante Debitoren/Kreditoren/Sachkonten/Artikel, welche keine oder nur alte Bewegungen haben, und trotzdem nicht gelöscht werden? Oder andere auffällige Abweichungen vom „Standard“, die bei nicht-löschen berücksichtigt werden müssen?

How do I find 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.

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?

In this case it is not: "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".

Estimated reading time: 15 minutes