NAVISION Technical Upgrade V4 -> V6 successfully? DB shrunk?

Fragment

Hi Community,

we did perform a technical Upgrade of the Navision-Client from V4 SP1 to V6 SP2 and we had positive effects like overall performance gain / better usability...etc.

What surprised us now, two weeks later, was when I checked the database size by coincidence. As I clicked on "Database" - "Information" I expected something like

120 GB database file size / ~80-85 GB used / percentage

But without doing any shrinkjobs of the DB (strictly not recommended anyway) the picture is

120 GB database file size / 40 GB used / percentage (35%)

We could identify that the reduction of size must have happened during or shortly after the upgrade.
The reduction of about 50% shocked us.

Question: Is this normal?

Thanks for your help.

Fragment

kriki

It happened DURING the upgrade. More precisely when you opened the DB with NAV2009R2.

It is completely normal.

4.0SP3 uses hidden tables to keep the totals of flowfields on all levels.
5.0SP1 and up uses indexed views to keep the totals of the flowfields. This is +- like just maintaining the last level in 4.0SP3. So a lot less data is maintained.

This is also 1 of the reasons why your write-performance increased. With 4.0SP3 each insert in Item Ledger Entry had to maintain at least some dozens of other records using SQL-triggers. Now it has to maintain only some other records and this is done internally of SQL (WITHOUT TSQL-statements), so it is also faster.

infonote

The log files where most probably taking a lot of space.
When you upgraded, the log files where no longer there.

In SQL Server there is a setting to set the maximum size of the log file, since they can become quote large.

kriki

True that the log file grows a lot with an upgrade. But the logfile size is not considered when calculating the % full of the DB.
Don't put a max size on the TL (or make it large, but not too large to occupy the whole disk). If you reach the maxsize of the logfile, your DB comes to a crashing halt until you fix the problem using TL-backup, putting the DB recovery model to simple (NOT RECOMMENDED!!!!!), increasing the max size of the TL file or added a TL file. The best is TL-backups.

DenSter

infonote wrote:

When you upgraded, the log files where no longer there.

No that is not true. The log files are still there, in fact SQL Server will not work without log files.