Navision 3.7 database cleanup

Ploeg · **Posted:** Thu May 10, 2012 9:40 am

Savatage wrote:

This doesn't really tell us what you tried.

I meant that I tried to delete a record from the table Item. There are lots of items which our client does not use anymore, so I wanted to delete those records. But when I try, Navision tells me to go do things to myself which I'd rather not do [-X

Savatage wrote:

Note: you will NOT be able to clear out "entries" like item ledger entries, customer ledger entries, vendor ledger entries, general ledger entries. These will go on growing forever and eventually all the cleaning of posted documents (Tables 110, 112, 114, 120, 122, 124 , etc) won't be enough.

I guess where pretty much up to an impossible task. The tables suggested above have the following size:

But when I look at the five largest tables in the database we see "entries":

Even if we would be able to half the size of the posted documents tables it would gain us 4 or 5 GB free database space. That is not a lot on the 188 GB (after optimizing) of the database. ](*,)

Savatage · **Posted:** Thu May 10, 2012 5:14 pm

Ploeg wrote:

Even if we would be able to half the size of the posted documents tables it would gain us 4 or 5 GB free database space. That is not a lot on the 188 GB (after optimizing) of the database. ](*,)

Understood that's why I'm testing Date Compression..
Which is probably a last resort type of thing. I'm not a fan of deleting data if you can avoid it.
You never know when it will come in handy.
If your company is willing to do the SQL switch then that's the better path.

viewtopic.php?f=14&t=53099
In my first Customer ledger entry test I toook 1 customer and compressed 450,000 entries into 17.
Bringing my test databasse from 84% used to 79% used. a good start.

kriki · **Posted:** Thu May 10, 2012 5:55 pm

Ploeg wrote:

G/L Entry - 56.35 GB
Item Ledger Entry - 24.61 GB
Value Entry - 20.85 GB
Stock Entry - 15.50 GB
Item Application Entry - 10.6 GB

I would first look to the keys in those tables. How much keys do have in each table and which fields are in those keys? And how much flowfields do you have in each key?
I think you have way too many keys in those tables. Some keys will probably be double or not used anymore. Deleting (or disabling) them might help a lot (but you really have to check they are not used anymore.

megawavez · **Posted:** Sun May 13, 2012 2:59 am

We tried compressing item ledger entries and ended up corrupted the db. I'd recommended avoiding this if possible.

Savatage · **Posted:** Mon May 14, 2012 3:12 am

megawavez wrote:

We tried compressing item ledger entries and ended up corrupted the db. I'd recommended avoiding this if possible.

That's perfect for my date compression post. You always here about date compression problems, but actual documented issues like your is the type of detail I think people need to know if they ever decide to take the compression path.

tdissing1966 · **Posted:** Mon Jul 02, 2012 7:48 am

I have made several Item ledger entries date compressions with success. We are running production only and use a modified date compression routine that saves space by applying a frequent commit.

Native NAV 5 SP1 DB 2.0 Objects 3.60
220GB allocated out of Licences 320GB
Tomas

mibuso.com

Navision 3.7 database cleanup

Who is online