I have a h2 database file and the file size has grown to 5GB. I have deleted some of data to reduced the size of the file. But the file size still remains the same even after deleting the half of the records from the database.

I have tried all the below options to reduce the database size but none of them have worked for me.

1. Executed shutdown compact (after closing all the connection).
2. Executed shutdown defrag.
3. Executed checkpoint.
4. Used the property MV_STORE=FALSE along with the database url.
5. Used the property RETENTION_TIME=30000 along with the database url.

MY connection String looks like below :

"jdbc:h2:"   <db file path>;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=10000;COMPRESS=TRUE;

NOTE :

1. We are closing the transaction which we are opened.
2. We don't have 5GB of data in the file.

Can anyone suggest me some work around or fix to reduce my database size

CodePudding user response：

Because data file is not reduced when you eg delete some records from the database. This is due to performance reasons. So h2 will either replace byte block in a file or append new one, but will not remove anything as it would require rewriting whole file.

Either you didn't compact as you think you did, or you did not delete meaningful (amount) data.

CodePudding user response：

Normally file size is much bigger than data size, because multiple versions of data are kept in it to accommodate concurrent transactions processing.

To reduce file size use "shutdown defrag" or "shutdown compact" (they are the same now), assuming you are using recent version.

This is the only way to bring file size down to data size (roughly). Upgrade, if it's 1.4.197 or older.

Your actions 3), 4), 5) do not make any sense to me. I would have RETENTION_TIME=0, if anything.