Huge Size

Peeps, The size of our Navision database is like growing at an alarming rate. 6 months ago it was like 50 gig, and now it is approaching 110 gig. I am running Nav 3.01b on top of SQL 2000 on top of Windows 2000 advance server. If I try to shrink the database using SQL manager, it goes down from 110 to 100 gig, and then as soon as people start to use it, it shoots back up. Has anyone any explantions for this growth. The userbase has not grown, infact due to layoffs, it may well have decreased. I checked inside Navision and noticed some data compression options within periodic activities, and was wondering what these do? Should I be running these, or do I need to be running some sort of procedures outside of Navision? Here are some actual facts :- Table name Item Tracking Entrey No of Records 4347146 Record Size 7,713 Size(KB) 32742888 Any help would be more then greatly appreciated Zed

Isn’t this like your 5th post on this subject? I find it interesting that a company with a database that size doesn’t know what compression is about… You definitly need to contact your reseller to see what you can do. Our per record size for the table you mention is about half of yours. Which means an optimization may help. But since your at the top end of the database size limit you probably can’t optimize (not enough space). A possible drastic step may be to do a database backup, setup a new database, then restore your backup. This will load your keys already optimized. BUT this will probably take something like 2 DAYS because your database is so large! CONTACT YOUR RESLLER!


Originally posted by speetz
Our per record size for the table you mention is about half of yours. Which means an optimization may help. But since your at the top end of the database size limit you probably can’t optimize (not enough space).

How much space does he need then? And what will be the result then (should he have enough space)

you can try optimizing a few tables at a time - small ones first. a bit off the subject but, it seems like a I have to optimize our Item Legder Entry every 3 days. When I optimize it goes to 98% and in 3 days it’s down to 78%. It doesn’t sound like a big deal but after I optimize this table my Database Used (KB) goes from 85% used down to 80%. Which is where I like it and this table constantly sucks up space. Anyone else have this problem? Is it normal? What causes the optimization to fall so quickly. Thanks

Probably most of the space is taken by SiftIndexes. Depending on your usage of Navision you might disable 90 - 95% of them.

OK…I missed the SQL part…since he’s on SQL all he needs to do is keep in mind that he needs as much free space in the database as the file he is trying to optimize. If we do the math on his table example, that one table uses around 33Gb of space. So he needs at least that much before he tries to optimize it. I have no idea how fast SQL is during an optimize process but I would plan on a good long time - like 10 hours - to optimize that one…

Never heard of the table “Item Tracking Entrey” (sic). AFAIK it’s not a standard table and therefore data compression will have no affect on it. This table alone, if your figures are correct, take up about 30% databasespace! The recordsize itself (>7K) also looks a bit suspicious - no wonder your database is growing at such an alarming rate. — Edit --------- Hmm, just realised that this table existed in previous versions. It has been abandoned know, though - or replaced by “Item Entry Relation”. Looking at it, a +7K recordsize still seems suspicious; surely some modifications must have been made to the table?

First off all CBC, if I knew about compression, do you seriously think I would be posting here asking for help[xx(]Doh! Also, no! This is my first post on this subject so ur mistaking me with someone else. What is the max limit my database can get to, before it goes belly up? I do not know if the table has been modified, I was never involved in the creation of our Navision systems. What is a SQL optimize?

Try to find (at your NSC or NTR) and read Navision document called “Performance Troubleshooting Guide”.

Doesn’t seem that weird tho - Item Tracking this means that every single item movement creates at least 4 to 6 lines min. Usually the lines are big since item tracking codes are usually long (like IMEI or sth). So if u have some 30-60000 items and got a lot of sales its not that much. Why you refuse to discuss it with you NSC seems really weird. With that big sales count you should have a good partner - please do not try to fix the db on your own - it is a bad bad idea (“I am a bad bad man” :)).

I am discussing it with my Navision Reseller as we speak, but they are really slow in responding most of the time, and I can usually find the answers quicker via the forums. Sometimes, armed with the info I get from u guys, I can then kick my resellers arse, and get them to respond quicker. I posted this topic on friday, and my reseller only got back to me today(monday) at about 3pm, and asked me if my transaction logs are not being truncated[:0] Sorry if I was being evasive[:)]

Update! Feedback from reseller. They have changed the DB growth method from 10% increase when SQL needs it, to a 1 meg increase when SQL needs it, as well as checking the autoshrink option. We have started running optimisation on tables from within Navision. However our reseller has not come up with this option yet[:0] File/Database/Information/Tables and then optimise. We are starting with the small ones, and will build up to the large ones out of working hours. Since my database is in SQL, where do I stand with running optimise on the 33gig table? Do I need to have 33gig spare in my SQL table, or 33gig spare on my hard disk where the DB resides? Or 33gig spare where my tempdb resides? Since my SQL DB increases whenever it gets full, would it automatically increase when I start doing the optimise? Any help greatly appreciated.

After starting this thread I expected you guys to post some positive comments on how to reduce the size of my 100gig database. How surprised I was[:D] I am so relieved to find out that there are so many people out there who are willing to help and offer their 2 cents. I just don’t know what I would have done without all the helpful comments and advice that you guys offered me[:p] Its not just the helpful comments that I really appreciate, its also the small help I can offer to other people by bringing this subject to the attention of other users who may also be having this issue[:p] And after all this advice was taken into account, and the really positive feedback I got from my reseller, who bent over backwards to help me, I have finally managed to reduce the size of my database to 55gig, so thats almost a 50% decrease, by carefully optimising certain tables[:D] I still haven’t changed any of my sift index settings, but who knows, with the lords help, it may even come down to approx. 30% of the original size. How pleased that would make me, you dare not even imagine[:)] Due to this fantastic reduction, it is no longer necessary for me to reach into the company pocket for cash to purchase more HDs and some pricey backup device, and hopefully, this will please my boss and keep the company ticking over. I will keep you all informed, and hope people learn much from my foray into Navision forum posting[8D]

Good for you, so I’m guessing you didn’t use navision standard “periodic activities” to reduce the size but went for the internal SQL options. Maybe i should rephrase my question: Could you explain the steps you did reducing the size and what helped a lot and what didn’t?

Well actually I did not do any sort of periodic activities, or as I had originally posted “compression” . All my tables were optimised using the FILE-DATABASE-INFORMATION-TABLES-OPTIMISE selection. I never went into SQL to do any sort of compression/optimisation, except to increase the size of the log file, with respects to the size of the table I was optimising, and some truncating of the log file as it grew in size after each optimise.

Sorry about the late answer. dont worry man you are not in critical situation. you can ask your NSC to bring you a newer Licence for 256 GB for free In addition: i dont think that Navision using SQL is under size limitation in the license.