mibuso.com

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!

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...

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.

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.

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.

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

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?

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.

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.

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?

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

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

fixed.

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

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