h2.db file keeps growing

Question

We're using H2 database (persistent) embedded within our application.

Even after we delete the tables, the h2.db file keeps growing.

We tried disabling transaction isolation (LOCK_MODE=0), disabled the query log (TRACE_LEVE_FILE=0), disabled the undo log, but nothing helps.

Is compacting the only option? Compacting will need restart of the DB which we cannot do.

Terminology

Answer 1

This issue got resolved. The issue was we were using connection stored in the servlet context. This connection was never closed. We changed the code to manage connections ourselves and close connections after tables are dropped (and reopen).

Terminology

Good to know it got resolved! I will keep my answer in case somebody else runs into this problem. – Kilometer

having the same issue, but difficult to apply this solution as I'm using spring hibernate and connections are managed by connection pool. don't know how to resolve, it grows 100kb to 30mb within few hours, without data being inserted. – Bulb

Answer 2

Using TRACE_LEVE_FILE=0 will only affect the file {databaseName}.trace.db, I suggest not to use disable tracing. The file {databaseName}.trace.db contains human readable error messages and warnings, and normally should be empty. If it is not empty, you might have a bug in your application.

The most common cause for growing database files is uncommitted transactions. Did you commit all transactions? With the newest version of H2 (1.3.168), the message "Transaction log could not be truncated" is written to the {databaseName}.trace.db file. But this requires you don't use TRACE_LEVE_FILE=0.

Please note empty space in the file is re-used, but the file doesn't shrink while the database is open.

Kilometer

all our transactions are committed. – Terminology

It would be great if you could add a line on whether the message appears only the first time this error condition is detected. I have a DB which grows each day, but clearly should not (recover->runscript reduces by a factor of 20), but the error message "Transaction log could not be truncated" is several days old, matching the first time this problem may have happened. (h2-1.3.176.jar) – Architectonics

@Architectonics first, you are using a very old version. Second, you should use the H2 mailing list / ask a new question / provide a patch. – Kilometer

Answer 3

The most efficient and easiest way I was able to shrink my H2 database is executing the following statement SHUTDOWN COMPACT followed by SHUTDOWN DEFRAG. It clean up unused spaces that were left after any deleted records.

        DatasourceConfig datasourceConfig = springContext.getBean(DatasourceConfig.class);
        System.out.println("H2 DB shutdown hook");
        Connection con = null;
        try {
            con = DriverManager.getConnection(datasourceConfig.getDatasourceUrl(), datasourceConfig.getUsername(), "");
            con.createStatement().execute("SHUTDOWN DEFRAG");
        } catch (SQLException var13) {
            System.out.println("Could not shut down embedded database" + var13);
        } finally {
            if(con != null) {
                try {
                    con.close();
                } catch (Throwable var12) {
                    System.out.println("Could not close JDBC Connection on shutdown" + var12);
                }
            }
        }

You can find more details about the SHUTDOWN statements. https://www.h2database.com/html/commands.html#shutdown

Cuckoopint