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!
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.
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)
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.