h2 db file size leak

[gaurav]

I've been playing with H2 and analyze the disk space usage and i've
noticed sort of disk space leak. Below is what i have done.

- Initially the file size of the db file is few KB's.
- Then i run a script to add a 6000 thousand rows to one of the
tables which grows the file size by about 5mb
- Then i run sql to see that the 6000 rows are there.
- Then i ran a script to delete all of those rows and checked that
all of those rows are not there in the db anymore.
- File size of the db stays the same.

Adding more rows(thousands of them) seems to keep adding up the file
size but deleting the rows never reduces the file size. I looked
around the h2 website could not find any obvious configuration that i
can do to have it free up disk space/reduce file size when rows are
being deleted.

Any ideas why this might be happening or how i can configure h2 to see
the my desired result?

Note: The connection to db is not closed during these set of
operations.

[archenroot]

Hello,

There is information in the database documentation that empty space is
reused automatically, but in case of deleting lot of data when you
want to shrink the database, you schould procede like this>
public static void compact(String dir, String dbName,
String user, String password) throws Exception {
String url = "jdbc:h2:" + dir + "/" + dbName;
String file = "data/test.sql";
Script.execute(url, user, password, file);
DeleteDbFiles.execute(dir, dbName, true);
RunScript.execute(url, user, password, file, null, false);
}

Ladislav

[gaurav]

is that basically just backing up the database, delete the db file and
restore from the backup to a new file ?

[Thomas Mueller]

Hi,

See also http://h2database.com/html/features.html#compacting "Empty
space in the database file re-used automatically. When closing the
database, the database is automatically compacted for up to 200
milliseconds by default. To compact more, use the SQL statement
SHUTDOWN COMPACT."

Regards,
Thomas