Navision 3.7 database cleanup

Ploeg

Hi there Navision gurus,

Our customer has reached the limits of the Navision native database (256 GB) and we would like to clean things up a bit since there is data in it from december 2001 until today. =;

So my question is: Is it possible to "close" years in Navision (i.e. 2001 to 2008) so that they get summarized and that, for example, old items can be removed?

Any kind of tips and pointers are welcome.

Thanks in advance!

kine

I highly recommend to upgrade the db to SQL. Of course, it will be a problem to check all data for valid dates, but with such a huge DB SQL will be better (if correctly tuned). Than you will not have a limit...

Savatage

Other than switching to SQL (after a complete backup, incase you need that data again)

For us, we do clear up some tables, that after a few years, are really unnecessary.

Tables 110, 112, 114, 120, 122, 124 are usually the first I hit. Like, We never need to see shipment info from 4 yrs or more ago. If a customer want's to complain about a package issue from 4 years ago..well good luck with that!
There are others too like bill of ladings we clear down & Lable line & other eship stuff.

I usually clear the oldest year so I can get an idea of how much space is being freed up per year of data in these tables.

Note: the backup is always there if we need to retrieve.

David_Singleton

Ploeg wrote:

Hi there Navision gurus,

Our customer has reached the limits of the Navision native database (256 GB) and we would like to clean things up a bit since there is data in it from december 2001 until today. =;

So my question is: Is it possible to "close" years in Navision (i.e. 2001 to 2008) so that they get summarized and that, for example, old items can be removed?

Any kind of tips and pointers are welcome.

Thanks in advance!

Your biggest issue is if you really mean what you say and you have reached the 256g limit OR if really you meant to say "you are getting close" because if you have hit it then its a huge job to make more space because of how the version principle works.

The compression routines in Navision are very dangerous, (which is why they have been progressively removed from newer versions). As Savatage says, you are best off starting by deleting necessary posted documents. Assuming you have enough free space to be able to delete.

After that you need to start deleting keys so that you can get the database size manageable and can then create a backup that you can then convert to SQL. But don't delay the conversion, when you are close to the limit of free space things get very difficult.

Alex_Chow

You can't switch to SQL if you're NOT current on the enhancement. If the client is on module based licensing and they did not buy the SQL Server option, they have to work within the C/Side database.

David_Singleton

Alex Chow wrote:

You can't switch to SQL if you're current on the enhancement. If the client is on module based licensing and they did not buy the SQL Server option, they have to work within the C/Side database.

Is the SQL granule no longer available to purchase on module based licensing?

Ploeg

Thanks for all the replies.

Navision shows us in de database information screen that 100% of the database size is in use. Also, the database already is under heavy surveillance and old shipments are removed every once in a while. Free disk space is not an issue here, there is plenty of room for more database

Our next step will be upgrading/migrating to a SQL Server database which, I suspect, will be a pain in the butt. Does anyone know if the newer versions of Nav have a database size limit in SQL Server?

David_Singleton

Ploeg wrote:

Navision shows us in de database information screen that 100% of the database size is in use. Also, the database already is under heavy surveillance and old shipments are removed every once in a while. Free disk space is not an issue here, there is plenty of room for more database

Ploeg you need to be very careful of this. Navision needs free space to work. Probably you are just over 99.5% full and rounding shows 100%. It is not an issue of disk space, its a question of database space. Everything you do in Navision needs free space. For example lets say you want to delete 5 years of old invoices. Navision takes a sort of snap shot of those and then virtually deletes them. Once the transaction is completed it then commits the data and releases the free space. But to do so it needs free space equivalent to the total size of all those documents. In your case 0.5% of 256Gig is still a lot, but as that 0.5% approaches zero you could get to a point where you can not do anything.

You couldn't even remove a key off a table. Unfortunately the free space is already probably too small to optimize tables, as that could release a lot of free space for you. Unfortunately that would also make the database slower, but that's the price to pay.

kriki

David Singleton wrote:

Unfortunately the free space is already probably too small to optimize tables, as that could release a lot of free space for you.

There is a trick to do that, but it is quite labour-intensive (and no-one should work at the same time).

I mark all the tables that I will need to optimize (and put the markedonly filter). Then I put a filter on tables with less than 10 records and optimize those (1 by 1 or some tables at a time). Generally these are no problem. Then I unmark them so I don't see them anymore.
Next I filter until 100 records and optimize those. Unmark them.
Then filter until 1000 records,.......
Then filter until 10000 records,.......
Then filter until 100000 records,.......
...

If my tables are not optimized, I can make a lot of free space this way even without increasing the database space.

Ploeg

Optimizing all tables went unexpectedly well. On a backup of the database, the database size that was in use went from 99% to 71%, so this saves us considerable amounts of space: from 255GB to 188GB.

Under ideal circumstances it would give us another 2 - 3 years before the database will be full again, but we won't be waiting for that to happen. Now I'm looking for a way to clean all the old data, so I would like to summarize (or in the worst case remove), for example, years 2001 - 2007. But how do I that? When I try to delete items which aren't in use anymore for years and years Navision starts to whine about posted invoices, shipments, orders, entries and all that stuff.

So, the summary of this reply and the actual question would be:
I want to clean the database from old data, but how do I clean sweep the tables and make sure all relations between records are being respected by my actions? Can it be done?

Alex_Chow

David Singleton wrote:

Alex Chow wrote:

You can't switch to SQL if you're current on the enhancement. If the client is on module based licensing and they did not buy the SQL Server option, they have to work within the C/Side database.

Is the SQL granule no longer available to purchase on module based licensing?

It's still available, but if you're not on the enhancement, you can't buy it.

David_Singleton

Alex Chow wrote:

David Singleton wrote:

Alex Chow wrote:

You can't switch to SQL if you're current on the enhancement. If the client is on module based licensing and they did not buy the SQL Server option, they have to work within the C/Side database.

Is the SQL granule no longer available to purchase on module based licensing?

It's still available, but if you're not on the enhancement, you can't buy it.

OK that makes sense, I guess it was a typo where you said

Alex Chow wrote:

... if you're current on the enhancement...

did you mean "if you're NOT current on the enhancement"?

Alex_Chow

fixed. 8)

Savatage

Ploeg wrote:

When I try to delete items which aren't in use anymore for years and years Navision starts to whine about posted invoices, shipments, orders, entries and all that stuff.

This doesn't really tell us what you tried.

Start with one table if you want like the "Sales Invoice Header"

go right to the table - filter on "Posted Date" add a date range (I suggest do 1 month first you you can see)
then select the results & F4.

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. But it's a good start and as you said you can add more years of non-sql life to your system

kapamarou

When I need space I first look at the change log entry. Do you have data there?

Ploeg

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:

110 - 1,05 GB 112 - 0.97 GB 114 - 0.14 GB 120 - 0.19 GB 122 - 0.13 GB 124 - 7 MB

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

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

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

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

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

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

Savatage

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

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