Purging Data - Date Compression

Hi All,

Has anybody ever written their own purging routines to reduce the size of a database rather than use the built-in Date Compression routines?

If so, can you post your reasons for doing so?

Do you feel that the Date Compression routines are perfectly adequate?

A while ago a colleague wrote their own purging routine for Customer Ledger but I can’t see much difference to the Date Compression alternative apart from obvious areas due to customisation within a database. Think

Many Thanks


Please give careful thought to using these functions or even writing your own.

Firstly don’t consider using the standard routines unless you have no added fields at all in the entry tables. Secondly concider why you are doing it. If the issue is data size, and saving disk space, then look at your system design, and see what makes sense to your business. A common need for compression is in a POS retail scenario, where the posted Item Ledger Entries cntain no significant data. I…e its important to know how many items you sell and when and which register, but that data is probably stored somewhere else, and really the Item Ledger and Vlaue ledgers are really ust for costing purposes. In this case it mkes sense to compress. But in other cases, look at the design of your system, and see if its possible to compress the data before posting.

In any case, I nver use the standard routines, and just ocnsider them as templates for building my own.

Thanks for the reply David.

The issue is generally saving disk space and my remit from my superior is simply to take the tables that hold the most records and take up the most space and reduce them by purging old entries prior to dd/mm/yyyy. The size of the database that this is to be applied to is about 40gb. Some of the sata goes back as far as 1998. Most of the of the major tables are customized in some way so I guess the best route to take is as you’ve suggested and use the standard routines as templates for building my own.


Also take special care with Item Ledger Entries. There were a lot of issues where compressed entries from older versions then would not allow upgrades. If you are going to compress (aka delete) Item LEdger entries, be very certain that they are not going to cause you problems. Personally I would try to convince the boss to buy more hard drives [;)]

Hi, I totally agree with David. I had a situation some 18 months ago when I upgraded a Client from 2.60 to 3.70. They were originally on version 1.3 and there was a bug in the standard compression routine which didnt manifest until compression was run prior to 3.70 upgrade which resulted in compressed Item Ledger Enties being deleted. Being as the Client was the Royal Navy, you can imagine the problems that this caused so be very careful with compression routines.

Thanks again for your replies.

I do like the idea of buying more hard drives though![:D]If I tell them I’ll charge a hell of a lot more to do the purging than the cost of increasing the storage size they might just go for it! [;)]

And from waay out in left field…

You could suggest setting up a new company and transferring only that data that is needed. (Keep the old company).

You would enter the opening entries just as though changing from SAP to Navision.

The biggest benefit is that you will have all the data if needed in the old company without the problems of wading through masses of very old data.

It is also an opportunity to get rid of old customer and vendor accounts, (things do get cluttered over time).

Mull it over, you may be surprised as the positive response from the users.

If the problem really is the db size (for backups etc) then archive the old company off to a spare location outside the backup set.

You could also keep the G/L entries for several years without all the sub-ledger entries.

More disc space is not always the answer, you may need to dig deeper to find the real concerns.