Hello friends, I have the table 32 (movs. items) with 70 giga bytes with sql 7.0, I want to optimize it from navision. that I space do I need to be able to optimize it?. Thank you
I’d say around 14GB. It’s roughly 20% free space of a database that size. It’s always a good idea to keep at least 20-25% free.
It is probably more difficult to estimate than that. Optimizing a table does a CREATE INDEX with REBUILD option for each active key you have. Since work tables are used in tempdb, you must ensure that tempdb can grow and there is enough space on the drive it is placed. If you have SIFT keys on the table, there will be a (temporary) copy made - in your database, not tempdb - of each table correpsonding to the SIFT key, which will be huge for that size table. Although each is deleted after use, this will not shrink the database, and additionally your log file will contain all records inserted into the copy, which also will not be shrunk. So, if there are no SIFT keys, it is your tempdb that will grow. If you do, it is also your database file(s) and log file, but by what amount cannot be determined without knowing the layout of the keys - since this determines the width of the SIFT table. After the optimization, I suggest you try to shrink your database and log files.
Hi. I had a quite same situation here. First, I would suggest date compression, if possible. Second, I think it may be reasonable for you to try to rebuld the indexes manually from Query Analyzer (CREATE INDEX … WITH DROP EXISTING) for those indexes that do not have SWIFT fields. For other ones, I recommend disabling + enabling them in Keys window. As for me the biggest problem was not the possible database size increase, but the time of optimization - table 32 is quite often used and surely if table size is 70GB then the downtime of the system can’t be very long. By doing the things in “steps” you can ensure that the downtime remains in some acceptable limit. For me, it took max 6 hours to rebuild one index (this time we used NT4, 4x500MHz proc., 2,4 GB RAM, SQL 7.0)
The following steps might help: 1.) Disable all keys in T32 except for the primary key 2.) Optimize the table 3.) Reactivate only one key at the time and save the table. – Repeat step 3 until all needed keys are restored.