Optimization Problem

vikram7_dabas

My clients Database increases too much Day By Day.Database is in SQL.Database size is 19 GB with in 8 months.If I will optimize that DB then wht will happen?What r advantages and Disadvantages of optimization?When I go in File->Database->Information there it shows me size 19 GB.Is it contains size of .mdf,.ndf and.ldf files?or separately?

navuser1

The optimization process performs two functions:

• For each Dynamics NAV table, the SQL Server indexes, other than the primary key,
are rebuilt to optimize their layout and usage.
• For each SIFT structure, any entries that contain zero values in all numeric fields are
removed. The removal of these redundant entries will free space and provide more
efficient updating and summing of SIFT information.




from w1w1isql

garak

which file is the larges? The Log file?
Read for example this post and search the forum for LOG File Or Transaction Log or Maintenance.

Regards

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

stryk

Hi!

First of all I'd like to encourage you to search this forum for "SQL Performance" - and maybe especially about SIFT and INdexes - and you'll get TONS of useful advices, hints and recommendations!

To measure the growth precisely you should check out the SQL procedure sp_spaceused (see "Books Online" for details) or the NDST (NAV Database Sizing Tool, part of SQL Resource Kit) - which is actually also using the sp_spaceused ...

Besides the technical aspects like SIFT and Indexes you should also look into the application/business logic itself: limit the number of "Dimensions", limit the number of "Analysis Views", use "Date Compression" etc. ...

Regards,
Jörg