db file size keeping growing in a long running program with active connections

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

1. Open a DB connection (never close it)
2. Keep adding and deleting records in the database
3. The data.db file size keeps growing after after sometime ends up
crashing the jvm

What is the expected output? What do you see instead?

As records are deleted, file space need to be reclaimed/reused. From the
documentation it looks like we need to close connections before compaction
can kick in. Our application is a real time 24X7 app and we cannot afford
downtime.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.1.105; JVM  1.5.10, Linux Ubuntu 2.6

Do you know a workaround?

In-memory works fine as long as the heap size is below 1.2 GB. If we enable
disk based db, then the db keeps growing until the file is over 1 GB and
then it breaks.

How important/urgent is the problem for you?

Its a scalability issue. The more we can store, the more we can scale in a
single instance

In your view, is this a defect or a feature request?

It depends on what the claimed functionality is. If its by design that we
need to close connections for compacting, its a feature request.

Please provide any additional information below.

Original issue reported on code.google.com by kpm...@gmail.com on 17 Feb 2010 at 11:32

lbehnke / h2database

db file size keeping growing in a long running program with active connections #165