Results 1 to 9 of 9

Thread: Database growth

  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a database that grows exponentially when I run it and the compact/repair tool does not decrease the size. I even tried just running it without changing any of the query parameters so that the data results would be the same. However, it continues to grow by the same size each time I run it. Any thoughts or suggestions? Thank you!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are there some macros or forms that open when you start the database? It sounds as if it may be creating additional records each time you run it. Also, what version of Access are you using, and what is the format of the database (.mdb or .accdb)?
    Wendell

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are no macros upon open; I just run the queries and open the report; we are on 2003 SP3. Thank you,

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by nthayer View Post
    I have a database that grows exponentially when I run it and the compact/repair tool does not decrease the size. I even tried just running it without changing any of the query parameters so that the data results would be the same. However, it continues to grow by the same size each time I run it. Any thoughts or suggestions? Thank you!
    If you are using Access2007, you need to install the latest Service Pack; it includes a fix for this specific problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    So it grows when you run the query, not when you open the database - that suggests the query is the issue. By chance is it a make-table query or more likely an append query. That would explain why the size of the database keeps growing. Try importing all the objects into a new empty 2003 database and see what size it turns out to be. If it is about the same as your existing database, then you have a temporary table or a permanent table that is growing each time you run the query. There is probably a design flaw in the process that is causing that, but to determine what it is would require looking at the design of the database tables and the query in question.
    Wendell

  6. #6
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I imported a table (so that is static for number of records); queried on that table (no parameters - just everything for result); got results which were saved in make table; repeated; yet each time my database grows in size even though the source and the results are exactly the same for number of records. I am really confused now...

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Can you show us what the SQL String for the query is? If it's a make table, then that makes a new table in the database which adds the size of the created table to your existing database, and that would make sense. If on the other hand it is an append query, then you are increasing the number of records in the table being appended to, and it grows each time you run the query.
    Wendell

  8. #8
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, here it is (tPracGroup00 is my table that I make and tQuery5a is the table I imported):

    SELECT tQuery5a.tkinit, ([tksect]*1) AS tksect1 INTO tPracGroup00
    FROM tQuery5a;

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That looks completely normal - although naming a table tQuery5a could be confusing to others. If you run that query it should be warning you that it will delete the existing tPracGroup00 table before it runs. Is that happening? Even if it is, the database will bloat with repeated runs. Access isn't terribly good at housekeeping disk space, the space occupied by the original tPracGroup00 table isn't being recovered. However a compact and repair should recover nearly all of the unused space. Your original post indicated that wasn't happening, and that's very unusual.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •