SYMPTOM

After purging sessions from the Runtime's H2 log database, the "stambiaRuntime\sessions\internalDb\sessionLogs.h2.db" file does not decrease in size.

Restarting the Runtime has no (or very small) effect on the file's size.

SOLUTION

Note: this is not a limitation, this is due to the H2 database's COMPACT mechanism.

Note2: We recommend to use a more robust RDBMS if your Runtime has to handle a big number of session. See the Designer's Help to know how to configure the Runtime with an external log database.

When deleting data from the H2 database, the file is not reorganized in real time: this requires a database shutdown. When shutting down, H2 "compacts" the file in order to reduce the file size. But this operation is limited to 200 milliseconds duration (default H2 behaviour).

The solution is to stop the Runtime, and start the H2 Database independently to execute a SHUTDOWN COMPACT command. Here is how to do:

  • Stop all the Runtimes which use this database
  • Open a command line and change directory (cd) to the Runtime's directory.
  • Execute this command to start the H2 database server:
D:\apps\stambia\stambiaDesigner_S17.2.10_20141007_120415\stambiaRuntime>java -cp lib\jdbc\h2.jar org.h2.tools.Server -webAllowOthers
Web server running on http://192.168.75.14:8082 (others can connect)
Please open a browser and go to http://192.168.75.14:8082
TCP server running on tcp://192.168.75.14:9092 (only local connections)
PG server running on pg://192.168.75.14:5435 (only local connections)
  • Open the H2 Web server's URL in a web browser
  • Set the connection URL to this: jdbc:h2:<pathToYourRuntimeDirectory>/sessions/internalDb/sessionLogs
  • Click "Connect", a SQL window should be displayed in the web browser
  • Execute the SQL command: shutdown compact
  • Please note that the console will probably show "Database is already closed" error after executing "shutdown compact" which is normal and does not mean that the command was not executed.
  • The sessionLogs.h2.db file's size should be reduced now
  • Close the H2 Server and restart the Runtimes.