Thanks for the analyse.
In the log file related to the DB with the issue, I found :
INFO | jvm 1 | 2012/02/05 19:12:14 | 2012/02/05 19:12:14 ERROR
[aseWorkerThread-cockpitDB] mserver.H2Listener - [DATABASE]
exceptionThrown sql=UPDATE MCTASK SET
name=?,description=?,idmccommand=?,isdeleted=?,creationdate=?,updatedate=?,timeout=?,issplit=?,idmccodeflowtype=?,idmcworfklow=?,parallelexecution=?,idmccodestatuspropagation=?
WHERE id = ?
INFO | jvm 1 | 2012/02/05 19:12:14 | org.h2.jdbc.JdbcSQLException:
Timeout trying to lock table ; SQL statement:
INFO | jvm 1 | 2012/02/05 19:12:14 | UPDATE MCTASK SET
name=?,description=?,idmccommand=?,isdeleted=?,creationdate=?,updatedate=?,timeout=?,issplit=?,idmccodeflowtype=?,idmcworfklow=?,parallelexecution=?,idmccodestatuspropagation=?
WHERE id = ? [50200-161]
Do you think that this message is related to the problem?
Also, do you have also analysed the 12GB database (under veryhuge folder)
because I'm interesting to know if the issue is the same?
Thierry.
Le vendredi 2 mars 2012 16:33:09 UTC+1, Thomas Mueller a écrit :
>
> Hi,
>
> I have downloaded the file now and ran the Recover tool. The database
> file is 3 GB, and the cockpit.h2.sql file is 9 GB. The end (tail) of
> the .h2.sql script is:
>
> ---- Statistics ----
> -- page count: 1552048, free: 24350
> -- page data bytes: head 486669, empty 15198569, rows 8567178 (38% full)
> -- free 7%, 113513 page(s)
> -- data leaf 0%, 11842 page(s)
> -- data node 0%, 843 page(s)
> -- data overflow 1%, 20810 page(s)
> -- btree leaf 0%, 1170 page(s)
> -- btree node 0%, 17 page(s)
> -- free list 0%, 7 page(s)
> -- stream trunk 0%, 2764 page(s)
> -- stream data 90%, 1401079 page(s)
>
> That means 90% of the file size is transaction log. This is what I
> expected. The transaction that was kept open was:
>
> ---- Transaction log ----
>
> -- session 257 table 115 - 728
> -- session 257 table 115 + ( /* key:794 */ 442, CAST('Discover and
> Provision Cisco_Others' AS VARCHAR_IGNORECASE), CAST('' AS
> VARCHAR_IGNORECASE), 95, 0, NULL
> , NULL, 0, 0, 34, 76, 1, 49)
>
> So it was connection #257 that did this: delete from table MCTASK
> (table 115, O_115) where key=728, insert into MCTASK (the values
> above). This connection was never committed or rolled back, so the
> complete transaction log has to be kept around.
>
> The database itself is quite small. I was you store lots of XML data,
> uncompressed, as VARCHAR. I suggest to use CLOB instead, and compress
> the data. This will save additional space and probably speed up
> things.
>
> You use quite many memory tables. I'm not sure if this is on purpose.
>
> Regards,
> Thomas
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/U3xAfgZeGSMJ.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
