Hello, so far i haven't got the time to analyse the application for open transactions, probably I'll do this next week.
As far as i recall after a full compact the file starts with a couple of megabytes, about 10 or 20. It only contains some data structure and a bunch of initial values. After that, data records get inserted and deleted frequently and the files grows up to 2-3 GB steadily, which takes a bit more than a month. At this point we are shutting down the application and the database and using a compact to get the file back to a couple of MB. I am aware that the DB-File has to grow to a certain amount until it will be able to live off the free space in the file. Thomas Mueller-6 wrote > The statement "shutdown compact" doesn't remove any LOB entries. If the > statement shrinks your database file, then the entries were already > removed before that. Yes, thats the problem. There is LOB data existing in the internal data schemes which corresponding data record in our tables do not exist anymore and those are the records that cause our DB-file to grow over time. After the compact those entries in INFORMATION_SCHEMA.LOB_DATA and the other two lob-schema tables are deleted. If I understand it correctly these records should be deleted at every commit or rollback. Is that statement correct? Assuming so - I will try to analyse the application and search for open transactions next week. Regards, dresa Thomas Mueller-6 wrote > Hi, > > Yes, it could be an open transaction. > > The statement "shutdown compact" doesn't remove any LOB entries. If the > statement shrinks your database file, then the entries were already > removed > before that. > > Please note the database file doesn't shrink if you delete data (but keep > the database open). However, empty space within the file is automatically > re-used. The database file only ever shrinks if you close the database > (close all connections or run "shutdown"). > > How large is your database before and after you run "shutdown compact"? > > Regards, > Thomas > > > > On Thu, Jan 30, 2014 at 7:21 AM, dresa_ < > florian-boeck@ > > wrote: > >> >> >> Hi Guys, >> >> it took me some time to start testing this change, but it doesn't seem to >> work as i expected. After deploying our System with the H2 Database >> Version >> 1.3.174 (2013-10-19) the same problem occurs. >> >> Our System works complete transactional, although commits do not clean >> the >> internal h2 LOB Tables after their corresponding data record is deleted. >> The >> only way to stop the Database Files from growing is the use a shutdown >> compact regularly. >> >> I hope someone finds the time to take a look at this again or give me a >> hint >> what we are doing wrong. >> >> Thank you very much in advance, >> dresa >> >> >> dresa_ wrote >> > Hello, >> > >> > we have issues with a steadily growing database file. I've already used >> > the recovery tool and tracked the problem down to the internal LOB >> > management. >> > >> > here some basic information: >> > - Database size after shutdown compact is only about 1 MB and it keeps >> > growing over time ( it reached 1GB a few days ago ) >> > - INFORMATION_SCHEMA.IN_DOUBT without entries >> > - H2 version 1.3.171 >> > - USED DB URL PARAMETER: MODE=Oracle;AUTO_SERVER=TRUE >> > - h2.lobInDatabase is set true >> > >> > Cause of the problem is a table that is used for messaging between >> > multiple systems and has one field with the type long raw ( which is >> > stored in the lob table ). >> > After an entry of this messaging table gets deleted the corresponding >> lob >> > remains in the table INFORMATION_SCHEMA.LOB_DATA. >> > >> > Is there a way to get rid of this no longer required LOBs without >> > executing and shutdown compact? >> > >> > Thank you very much in advance, >> > dresa > > For more options, visit https://groups.google.com/groups/opt_out. -- View this message in context: http://h2-database.66688.n3.nabble.com/DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4028421.html Sent from the H2 Database mailing list archive at Nabble.com. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
