Hi All,
I am with an end user whose Item ledger entries(ILE) has grown phenomenally, I am tasked with finding a solution to reduce this, i.e delete ILE’s prior to 18 months. There used to be date compression of ILE process which seems to have disappeared in the latter versions. We are on 5.1 executables on a 4 DB. I wonder any one of the forum users have done something like this or could shed some light on the best practise.
Regards
Moraj
It disappeared in NAV 5, fortunately, I would say! A customer did have some very bad experiences by compressing item legder entries in NAV 4 SP3, and at the end I had to use an old backup (only one copy existed!), pick some item legder entries, deleted by the compression, and put them back into their database. Timeconsuming and not an easy task!
I can’t recall the exact problems at the moment, but the “cost-batch” was one issue - it didn’t handle compressed entries!
Hi
Thank you for your quick reply . I have read of many comments on adverse affects on the date compression. Any suggestion on best practise to reduce the number or records on ILE will be greatly appreciated.
Rgds
If you are seeing decreased performance I would check other things before deleting data. Are you on SQL? Have you done any index tuning? Checked all of the code to make sure you are using the right keys in customizations?
Deleting data should be a last resort.
Hi Matt,
Yes we are on SQL. Size of our DB is 700 GB ILE is as Follows.
Table No. Table Name No. of Records Record Size Size (KB)
32 Item Ledger Entry 128848236 911 114566296
Hi Matt,
Yes we are on SQL size of our DB is 700gig. ILE shows as follows.
Table No. Table Name No. of Records Record Size Size (KB)
32 Item Ledger Entry 128848236 911 114566296
Rgds
700GB? Seriously?
How are you tracking your inventory? Serial / Lot? That’s an insane volume of orders.
Hi Matt,
It is big. we dont use tracking. We are in highstreet retail. whole purpose of this excise is to reduce the time taken to re-build idexes on weekend currently it takes 8 to 10 hours.
Matt:
For a “highstreet retailer” 130M entries in ILE are normal, taking into consideration they are using Navision for years (moraj mentioned he wants to leave only last 18 months) AND the daily amount of Item transactions for them counts in tenths of thousands.
moraj:
it should be not ILE only, many more tables in such case should have millions of rows, but these CAN be compressed or even deleted (posted invoices for example).
Deleting ILE might be done not in timescale, but for discontinued Items ONLY - imagine what will happen, if you cut off the oldest entries for an Item you introduced 3 years ago and continue to sell up to now? Maybe that is the reason, why ILE compressing is quietly removed by MS…
Hi Moraj,
is this LS Retail?
Actually the size is pretty good, I am right now workign on a DB with “only” 15 million ILEs (almost 1/10th of yours) and the DB is 450gig. So it looks like the db has been tuned already, is that so?
As to compression and deletion of ILE, yes that is possible, but not using the standard Navision tool, that one just had too many issues. In fairness to PC&C though, nearly all the issues I ever had to fix were because of customers that used an unmodified version of the tool on a heavily modified database.
Hi David
Yes it is LS retail but our tills are retail Java as LS retail data director wouldn’t have been able to handle our volume of transactions. Retail Java with an interface to import data to Nav has worked exceptionally well. I used to work for another end user who used LS Retail on stores where I had to be available for week end support.
Yes DB has been tuned. Our most pressing issue is the time taken for backups and sql maintenance on weekends. hence reduce the size of the DB. Where can I can get hold of this new tool?
I am also thinking if it is worthwhile to make this an opportunity to migrate to Nav2009. (we are currently on Nav 4 DB on 5.1 executables.) then we could take opening balances.
This is the safest way to clean up those hundreds of Mb [:D]
But - upgrading for such client will be hard, too, as you will not have a “second shot”. Everything must be very thoroughly prepared and planned, as you will have only one weekend to change production installation, imagine, what happens if the shop can not open on Monday because system is dead…
Hi,
Shops arnt a major issue as our tills dont use Navision. If we are to take this approach i will have a parallel enviroment.
And here I was thinking our 100+GB database was big [:$]
He he,
just for comparison, last night I was tuning a database, and removing unused indexes and sifts off four tables, that REDUCED the size of the database by 108gig.
And right about now in a good Yorkshire accent someone needs to say “When I were a lad…”
The problem is that this might give you a 12 month reprieve, but then next year the problem is back. So a long term archiving solution is required.
Well, it’s not like I thought was big for any database, just for a NAV database. But yes, I still have much to see and learn.
ah a cup of cold tea!
Hi David,
Yes this is something we may have to do every 18 months.
[:D]
SQL 2008 limit is 524TB… I get lost in zeroes trying to calculate how many my first HDDs of 20 MB (in a PC XT) it is…