Hi, I thought the statistics are taken _after_ compacting, sorry about that.
The statistics show that most of the pages are free (free 91%, 67%). You don't seem to have open transactions (stream trunk and data). When (temporarily) more space is needed, the database file grows more than needed (to avoid many file resize operations). While the database is open, the file does not shrink, even if you drop all tables. Empty (free) pages are re-used, but the file doesn't shrink. It only shrinks when the database is closed (that is, all connections are closed). Shrinking means pages that are still used are moved to the front of the database file, so that all free pages are at the end of the file. At the very end, the database file is truncated. How do you close the database? Do you use "shutdown immediately"? In this case, the database file is not truncated. If you simply close all connections, or if you use "shutdown" or "shutdown compact", then the file should be automatically truncated. Regards, Thomas On Thu, Jul 11, 2013 at 5:50 AM, <[email protected]> wrote: > There were two files actually..since I did it twice. The statistics are as > below:- > > > ---- Statistics ------ page count: 954655, free: 776787-- page data bytes: > head 13860963, empty 26006602, rows 35134291 (66% full)-- free 91%, 876991 > page(s)-- data leaf 3%, 36622 page(s)-- data node 0%, 268 page(s)-- btree > leaf 3%, 36567 page(s)-- btree node 0%, 570 page(s)-- free list 0%, 52 > page(s)-- stream trunk 0%, 8 page(s)-- stream data 0%, 3574 page(s) > > and the next set is: > > ---- Statistics ------ page count: 235708, free: 164636-- page data bytes: > head 13268512, empty 24936708, rows 33759452 (66% full)-- free 67%, 159364 > page(s)-- data leaf 14%, 35139 page(s)-- data node 0%, 267 page(s)-- btree > leaf 14%, 35338 page(s)-- btree node 0%, 568 page(s)-- free list 0%, 15 > page(s)-- stream trunk 0%, 9 page(s)-- stream data 2%, 5005 page(s) > > > P.S:I had already posted this. I guess you missed it. :) > > On Tuesday, July 9, 2013 9:31:40 PM UTC+5:30, Thomas Mueller wrote: > >> Hi, >> >> OK, could you post the 'Statistics' part of that file? >> >> Regards, >> Thomas >> >> >> >> On Tue, Jul 9, 2013 at 2:11 PM, <[email protected]> wrote: >> >>> I posted in both to get a wider reach. Would be using only one in the >>> future. >>> >>> I had stopped both the applications and then applied the Recover tool >>> option to generate an SQL file with the same name as the DB. This was done >>> when the database file was of large size. One thing noted was that the size >>> of the SQL file too was very large close to the .db file. >>> >>> On Tuesday, July 9, 2013 12:01:13 AM UTC+5:30, Thomas Mueller wrote: >>> >>>> Hi, >>>> >>>> You have sent the same question to http://stackoverflow.com/**qu** >>>> estions/17488935/continuous-**in**crease-in-h2-db-size-after-**dro** >>>> pping-and-loading-same-**data-**repeatedl<http://stackoverflow.com/questions/17488935/continuous-increase-in-h2-db-size-after-dropping-and-loading-same-data-repeatedl> >>>> >>>> Please don't use _both_ the Google Group and StackOverflow at the same >>>> time for the same question. Use one or the other. >>>> >>>> Did you run the recover tool _before_ you restored it? To find out what >>>> is using the disk space you would need to run it _before_ that (when the >>>> database file is large). >>>> >>>> Regards, >>>> Thomas >>>> >>>> >>>> >>>> On Mon, Jul 8, 2013 at 8:53 AM, Noel Grandin <[email protected]>wrote: >>>> >>>>> This is pretty much guaranteed to be because of open transactions. >>>>> Unfortunately there is no easy way of finding them, short of >>>>> monitoring your own code to check that it is either using AutoCommit or >>>>> calling commit()/rollback() timeously. >>>>> >>>>> >>>>> On 2013-07-08 07:31, [email protected] wrote: >>>>> >>>>> I have an H2 db named temp.h2.db which is accessed by two >>>>> applications. The first one accesses it through embedded mode and the >>>>> second one through server mode. Through the second application I load data >>>>> into the database. But even when I drop the previous values and load the >>>>> same data repeatedly, the db size increases. From about 200mb, it >>>>> increased >>>>> to about 2Gb. This happens even when I drop all tables and load a fresh >>>>> set >>>>> of data (which is almost of the same size as the previous set of data). Is >>>>> this a bug? >>>>> >>>>> >>>>> -- >>>>> 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 h2-database+unsubscribe@**google**groups.com. >>>>> To post to this group, send email to [email protected]. >>>>> Visit this group at >>>>> http://groups.google.com/**group**/h2-database<http://groups.google.com/group/h2-database> >>>>> . >>>>> For more options, visit >>>>> https://groups.google.com/**grou**ps/opt_out<https://groups.google.com/groups/opt_out> >>>>> . >>>>> >>>>> >>>>> >>>> >>>> -- >>> 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 h2-database+unsubscribe@**googlegroups.com. >>> To post to this group, send email to [email protected]. >>> Visit this group at >>> http://groups.google.com/**group/h2-database<http://groups.google.com/group/h2-database> >>> . >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>> . >>> >>> >>> >> >> -- > 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. > > > -- 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.
