Managing Huge Change Log Table

najja
najja Member Posts: 67
edited 2010-05-04 in NAV/Navision Classic Client
Hello Experts,

We have enabled change log for each and every fields of the database to meet some statutory reporting requirement. The result is a huge Change Log Table of more than 100 gig for that table alone (this is my approximation). I was wondering whether moving the Change Log Table alone to a different, exclusive file group and placing that particular file group in a seperate physical volume will allow me to optimise the performance of the entire system. There is no way we can purge the change log data. Please advise. Is there any other method to manage huge tables in NAV.

Najja

Comments

  • einsTeIn.NET
    einsTeIn.NET Member Posts: 1,050
    Maybe this could help you http://www.mibuso.com/forum/viewtopic.php?f=23&t=40840.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Karenh
    Karenh Member Posts: 209
    The company where I work the most also had a very large change log table. Last year, when we moved to version 5 SP1, we switched to Lanham's History add-on. In the upgrade, we purged the change log table. Users can go back to the pre-upgrade database if they wish to see the history.

    In addition to the large size, we experienced frequent table locking delays as every transaction required multiple additional history table records. This was with the native db.

    Lanham's add-on uses multiple tables rather than one. There is one history record table for each table for which history is tracked. So we no longer have one huge table and the locking problem has disappeared. Users also like the interface for viewing history records much better.

    Lanham markets its product as History Management Basic, and then sells additional tables in blocks of 50. Rather than use a history table for tables that are seldom changed, such as set up tables, we use the Change Log for those.

    One drawback with Lanham's add-on is the additional programming overhead when you add fields to tables. If you can to maintain history on those new fields, you must go through the process of creating new history objects. I modified this process so I can create new history objects only for selected tables, instead of all tables for which history is tracked.

    www.lanhamassoc.com has more information.

Categories