I would like to know what you SQL experts recommend as the ideal SQL 2005 database growth settings are?
Should I just accept the default 10% or? Should I accept automatically growth at all (afraid that if performance will stop completely whilst database is growing), and if I don’t is there a way where I can have an email warning when the database is required to be expanded?
Are there any difference from Navision version 4.0 SP3 or NAV 5.0?
i would say test would be if you accept no database growth - you should declare a database size which can handle the growth for the next 2 years.
For the transactions log file you can create an “SQL Server Performance Condition Alert” where you can analyse the percent log used and then you can automatically expand the transaction log file.
For the used data base file size i an not sure in this moment if you can handle this also in an easy way with sql server - but i have already written an script where i measure the database groth - and then you can also start some SQL-scripts to exapnd the database file size or send an e-mail.
I hope that is understandable
Well declaring a database which can handle the growth for the next 2 years is really unrealistic! Our predicted size in two years is going to be around 2 tera byte (based on the current growth + the planned number of new countries/companies/users going to be added - our company has an annual growth of between 30 and 50% in countries where we are planning to install NAV)!
I know in my old company we had some pretty good settings. I just don’t remember how they were…
This is my personal view:
In general I use 50MB as autogrowth for DB and TL.
-The DB-occupation should always be monitored. So when it gets over 80% of usage, the administrator should plan to grow the DB-size when no-one is working. Because when the DB-file is growing, nothing can be done.
-The TL I create big enough so in theory it should never grow.
The 50MB autogrowth I put in case of emergency:
In case the admin sees he should increase the DB and he plans to do it the day after and he is ill for some weeks, at least the users won’t get the message that the DB is full (like happens a lot with the Navision-DB) and not be able to work until someone enlarges the DB.
Of course, it will slow down everything in these cases but at least they are not completely blocked.
When the growth is always only 50 MB you will become very defragmented disks. It is better to enlarge the data file only with only with few enlagerments of the date file - with big database once a month. You can avoid that the data file runs out of space when you log the date file usage every week to calculate the increase.
I know this is the danger. But I use it like an insurance : you take one in the hope you never need it!