Hi,
A customer of mine is running a NAV based application. The dB in use is SQL Server 2005. Now, after about 7 stores and 8 months of operations, the client has a data file size of 225MB and huge log and .ndf files of sizes 30GB and 96GB respectively. Isn't something wrong? Aren't the log and .ndf files too large? Appreciate all pointers!
Hi SN,
welcome to the "Dynamics User Group"
Well, wether the db size is plausible or not depends on the transaction volume which was processed during the past 8 months; e.g. how many orders, lines, ledger entries, etc. have been created ...
Which NAV version do you use? If you're running a NAV which is using SIFT, then a lot of additional records might be created, thus SIFT optimization and maintenance would be crucial (http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx).
Further, if a wrong Index Fillfactor is applied (e.g. due to a bug in SQL Server 2005 Build 1399!!!) the db could grow extremely fast!
But regardless of the datafile size, the Transaction Log should be kept at a reasonable size; which depends on the Recovery Model and the Backup Strategy: if you run frequent TLog backups, then the ldf should not be that large.
Best regards,
Jörg
Joerg A. StrykSTRYK System ImprovementNAV/SQL Performance Optimization
Don't forget to the post(s) that solved your problem. This credits the experts who helped, earns you points and marks your thread as Resolved so we all know you have been helped.
Thanks, Jorg, for that prompt reply! The Nav being used is 4.0. And what would be the right fillfactor?
Further, the customer keeps referring to the ndf file as the index file - that is not entirely correct, right?
Regds
SN
A general rule for sizing the transaction log files is that they should be large enough so that the system does not auto-expand them during operations. If they are auto-expanding you have two solutions, either increase the file(s) size or perform more frequent transaction log backups. If the increase is being caused by a single transaction then the only choice is to expand the file(s) size. While a typical systems normal operations may not require a large log space, there may be periodic batch process that consume more then usual.
Since the transaction log is on its own dedicated disk (it should be) its size really shouldn't be an issue. Provided that it is not too large for the disk and the system does not attempt to expand it beyond the disk capacity. If your system does require a large log file, you are much better off manually creating the file on a freshly formatted disk instead of allowing the system to grow it over time.
With the above being said, I would be very surprised if your 30 GB database needed a 96 GB log file.
As an example, one system I work with has a 60 GB transaction log file. Log backups are run every 4 hours and average a few hundred megabytes during normal operations. However there are weekend batch processes that will consume 30 GB or more of the transaction log. (The DB is 240 GB)
SN: Thanks, Jorg, for that prompt reply! The Nav being used is 4.0. And what would be the right fillfactor? Further, the customer keeps referring to the ndf file as the index file - that is not entirely correct, right? Regds SN
With NAV 4.0 it is crucial to optimize the SIFT structures (as briefly described in the previously mentioned link)! Once you have reduced the number of "SIFT Buckets" to a necessary minimum, the database size should decrease remarkably.
With NAV a good fillfactor is between 90% and 95% which e.g. could be applied via the "Rebuild Index" task of the "Maintenance Plan" feature (to apply individual/optimized FF you need specific tools). Have in mind, that with this MP task yu have to define the amount of free space, hence, to set a FF of 90% you need to assign 10% free space.
Caution: Due o a bug in SQL 9.0.1399 a "Free Space" of 10% results in a FF of 10% - and this raises "hell on earth"!
Well, out of the box the ndf files are simply database files which store all & everything, but of course they could be used for specific purposes. Thus, it is possible to define various "File Groups" for specific purposes, e.g. to segragate the Indexes. This could be only applied with SQL features, not from NAV.
Use "Shrinkage option" in SQL Server.which drastically reduces the log and .ndf files
One can shrink the database files in SQL Server itself.
Sudhakar: Use "Shrinkage option" in SQL Server.which drastically reduces the log and .ndf files One can shrink the database files in SQL Server itself.
Never shrink files on a regular basis. This is a great way to fragment them on the disk and impact performance.
It seems you have activated the Change Log options for all the tables and fields.
Huge amount of data is generated in such a case.
You need to remove the Log Files from SQL on regular basis and there is a procedure for that which you should be able to find out on the forum.
Best Regards,
DD