Alter Database Settings Issue

aavio

hi,
Our Client systems are reported to be slow these days. i think its due to very low free space. Currently database Used :88-90%.
We are working on nav 5.0 with sql server 2005, DB settings are :
Primary datafile is 120MB, File Growth -1280% & unrestricted growth TRUE
Secondary datafile 1699MB, File Growth -10240KB & unrestricted growth TRUE
Log File 5MB, File Growth -10240KB & unrestricted growth TRUE
Recovery Model :Simple
Auto Shrink :True
Record Set :500

i had some doubt regarding it,
When the database used % comes around 90% , manually we increase database size to around 35% free space. After some momment database used % comes back to previous size. is it beacause of Auto Shrink property?

when will the File Growth takes place, is it only when db size is 100%??

i just read "You must set the database Single user option before you alter the database." why its so? earlier we didnt followed this rule, will there be any issues based on it?

in the Installation & System Management: SQL Server Option for the C/Side Client manual it is said that for expanding database "Increasing the size of the existing data files or adding new data files, in order to allow more data to be stored in the database. If secondary data files are present, you will only need to increase the size of the primary data file when the catalog which it contains has become too large.". what does this catalog mean, :? below that it also mentioned that "When you are using secondary data files, you cannot create more space for storing Dynamics NAV data by simply increasing the size of the primary data file. You can create more space for storing data by increasing the size of the secondary data files which contain Dynamics NAV information."

if FINDSET is called in a 10000 record table, and if there is no update process, it will cause performance issue right so how to overcome it, instead of findset :-k

thanks in adv

stryk

Wow ... hm, where to start ... :-k
First of all you should search the forum for "SQL performance" and you'll get tons of useful advices, hits and recommendations ...

Just some brief facts:

+ A SQL DB - with NAV the "Data Filegroup 1" - should have about 20 to 25% free space
+ Growth should always be fixed; e.g. 250MB
+ "Auto. Shrink" is a real killer and could causes maximum index fragmentation - NEVER USE THIS!
I guess you just want to shrink the "Transaction Log" file; this should be only done if really necessary using the DBCC SHRINKFILE feature
+ With "Simple" Recovery you cannot make Log Backups, hence you are not using a very smart and important security feature

Regarding the DB files:
With NAV you usally have two database files in two different filegroups: PRIMARY and "Data Filegroup 1" (Standard). Every SQL DB must have a PRIMARY group; this is where some system/meta data is stored. With NAV, all other data is stored in "Data Filegroup 1". PRIMARY only grows moderately, and only if you add new objects or indexes; DFG1 is the important one!
Splitting up DFG1 into multiple files is only feasible if you could place each file on a separate physical drive; else it's somewhat pointless. IF doing that, you should look ionto the DBCC SHRINKFILE EMPTYFILE feature to re-distribute the data among thevarious files.

FINDSET calls the data in "packages" depend on the NAV setting "Cache Record Set". Standard is 500 which is OK for "Fat Clients". When using Terminal Server or Citrix it might be feasible to decrease this to 300 or 200 (with NAV 2009 RTC it is 50).

Hope this helps you for a start; have in mind that there's a LOT to do o improve the performance ...

Regards,
Jörg

David_Singleton

stryk wrote:

Wow ... hm, where to start ... :-k
First of all you should search the forum for "SQL performance" and you'll get tons of useful advices, hits and recommendations ...
...
Regards,
Jörg

I think he shoud start by calling an expert (Jorg perhaps ) for some professional help.

When you see a server set up like that, you know that there are even more problems waiting to happen.

What scares me the most is....

aavio wrote:

Our Client systems are reported to be slow these days. i think its due to very low free space.

As a partner selling Navision, you don't think, you have to know. You should have experts in your company that look after your clients. [-X

aavio

David Singleton wrote:

As a partner selling Navision, you don't think, you have to know. You should have experts in your company that look after your clients. [-X

David we had such a expert, but for getting more clarification only i had posted the query...

stryk wrote:

Hope this helps you for a start; have in mind that there's a LOT to do to improve the performance ...

Regards,
Jörg

thanks stryk for your valuable input =D> ....we are checking & will let you know.

David_Singleton

aavio wrote:

David Singleton wrote:

As a partner selling Navision, you don't think, you have to know. You should have experts in your company that look after your clients. [-X

David we had such a expert, but for getting more clarification only i had posted the query...

Sorry Aavio, but I think that if you had such an expert they would not have set up the server like this in the first place. Or do you have an expert, but that expert did not look at this server before it went live.