DB regression when exiting with SHUTDOWN COMPACT?

torlpedo

unread,

Dec 9, 2021, 9:01:13 AM12/9/21

to H2 Database

Hi all,

Creating a medium sized database (300MB with 800MB disk usage) and then exiting with SHUTDOWN COMPACT makes the database read-only, making impossible to modify the data via DML commands.

I tried opening the DB in read-write mode in the connection string using ACCESS_MODE_DATA without success.

Has everybody else encountered the same issue?

BR,

Ivan

Andreas Reichel

unread,

Dec 9, 2021, 9:09:42 AM12/9/21

to h2-da...@googlegroups.com

Greetings!

Unfortunately, this seems to be a kind of normal: As long as the DB is running, it will keep "leaking" file space .

Our virgin 2 GByte DB grew to 400 GB eventually, after running for months. "Shutdown defrag" frees a lot of the claimed file space, but not all and it depends on a restart.

Best solution I found so far is to Extract the DB to script and rebuild the DB from script. This brings the DB reliable down to the actual size and also provides nice backup snapshots in case of corruption.

(SHUTDOWN DEFRAG corrupted our databases last year, but seems to have gotten much more stable recently -- just a "gut feeling" though.)

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/89d53406-be8e-4a15-9f9e-bf4f24e67092n%40googlegroups.com.

torlpedo

unread,

Dec 9, 2021, 9:24:13 AM12/9/21

to H2 Database

Hi all,

Thank you Andreas for confirming. This is strange... With h2 version 1.4.200, although having the size creep, the database file ended up well formed and a posterior re-connection allowed DML changes on data...