NAS increasing database size on Windows Server 2008

lzr

We recently installed NAV 4.0 SP3 with NAS on our customers server. Now I have noticed something very strange: The NAS is setup to export data into text files (no import). The database size is increasing a lot while NAS is running and after just a few days the size has increased about 10G. When restarting NAS the database size immediately drops to normal again, and then the same behavior over and over again.

Anyone had any similar experience with NAS?

Slawek_Guzek

Hi,

I'm assuming that you're talking about SQL database, and the database i in simple recovery model.

Most probably you have somewhere in your code explicit LOCKTABLE. Or maybe your NAS executed code writes something to one of the tables (why not ? yuo've said that it only exports the data but perhaps also logs what and when was exported), which leaves you open transaction(s). Since the transaction)s) remains open SQL log file can't be reused, all subsequent transactions are logged after this one and kept in log file. As soon as you stop NAS the transaction is closed and log space is marked empty and reusable.

Regards,
Slawek

lzr

Ohh, sorry it is native database.

Thing is we have had NAS running the exact same code when the customer was on NAV 4.0 with Windows server 2003.

Thanks for your answer

Slawek_Guzek

Mmm, strange..

Are U using a report to export the data? Perhaps it has TransacionType set to Snapshot, and report variable is not cleared after finishing the export process...

Regards,
Slawek

lzr

Yes, indeed very strange. There are only two codeunits running during the export. None of them runs a report.

PurPaySetup.GET;
WHILE (TRUE) DO
BEGIN
  SELECTLATESTVERSION;
  PurPaySetup.GET;
  PurPaySetup.TESTFIELD(UpdateIntervall);
  ms := PurPaySetup.UpdateIntervall * 1000*60;
  UpdateOutput.RUN;
  CODEUNIT.RUN(50022);
  SLEEP(ms);
END;

Slawek_Guzek

From the app point of view this is one countinuous process running all the time. It might not be directly responsible for database grow (in terms that it doesn't write anythig to the database), but it actually might force other process to create and keep new copies of databases pages - due to SELECTLATESTVERSION

That's my guess only. Perhaps it sounds stupid but did you try to insert COMMIT just after CODEUNIT.RUN(50022) ?

Regards,
Slawek

kine

For me better is to use NTimer to run the process, e.g. in interval of "ms". It will be easy to restart (you will not need to kill the NAS anymore), it will work correctly with transactions, you can catch the errors (OnError event) etc. Try it, it is much better.

Slawek_Guzek

I agree, using NTimer automation is correct solution but it is, I would say, a 'fundamental change' comparing to curent solution.

Definitely worth trying even if COMMIT helps.

Regards,
Slawek

lzr

Thanks for your answers, I will try to make a commit afterward, I have to check the code first though. If it doesn't work I will make the ntimer. At the moment I have put a automatic restart of the NAS once a day ](*,)