Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Incredible BE bloat (2000+)

    I do some contract work for a software house, and they have a client that has (apparently) all of a sudden started experiencing incredible bloat on the BE. The db will start the day at 150MB, but in just a few hours it will be close to 2GB! It will compact back down to 150MB.

    Now, this software is in use by maybe 300 clients, and no-one else has had this problem. At this particular site, everyone is using Terminal Services to get to database (maybe 10 users total) and everyone has separate FE; but other clients with same configuration have had no problems. Everyone is using Access2003; backend was at Access2000 and I believe it was even converted to A2003 but problem persists.

    Client claims that there have been no recent changes; that is, no server updates, etc. I believe it is Windows Server 2003.

    I think the server is wowfully underpowered, but I still can't get around that this has happened all-of-a-sudden. If it is the application, then maybe it might be something that is stuck in a loop writing and deleting info to a table. But we can't figure out what that might be.

    We are in process of having them systemically trying to isolate what might be causing this (allowing 1 user at a time to log-in and start work, etc.). But in the meantime, anyone have a clue as to what might be causing this?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Incredible BE bloat (2000+)

    Keeping the backend at Access 2000 format might be better, the Access 2002/2003 format is more prone to bloat.

    Is there code that creates temporary tables, or code that moves records between tables, etc.?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    There is some temp tables (they are really permenant, just the data in them frequently gets entirely deleted and rewritten during some report processing). We have considered that, but why is no-one else having this problem? With no reports of any user "hanging up", there is no reason to believe any user is instigating a process that is causing this massive bloat. Plus we can't even figure out what process could even do this anyway!

    Right now, we are just trying to learn more. For example, we know the size increase is rapid; but we don't know if it is a steady increase over the time period or just 1 big jump. So, we are just monitoring the situation. We will also try putting in a test database to see if it bloats also. I'm wondering if it could be the server just allocating the file the huge amount of space? Quite possibly, Access itself doesn't even know it has that much space assigned to it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Incredible BE bloat (2000+)

    The only thing that I know that will cause bloat in a backend is someone running queries local to the backend. I doubt that your application has a bunch of stuff set up to execute in the back end, so that means the client is doing it themselves. Is that a possibility?
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    As you suspected, we don't have anything running directly in the BE . I don't think it is a possibility that anyone is directly working in BE. I don't think anyone there has knowledge enough to actually do anything from BE, since they would have to create their own queries, etc. I keep coming back to "why this one site"? I just have a gut feeling it is server related, either that or last night 's Burrito Grande is working on me!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Incredible BE bloat (2000+)

    > last night 's Burrito Grande

    But that would cause YOU to bloat, not the backend database, I hope! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    >>But that would cause YOU to bloat, not the backend database, I hope! <<

    OK, I think we've hit on a solution! How does one pour Pepto Bismol into a database?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incredible BE bloat (2000+)

    Through the flux capacitor.... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Duh....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    >>Through the flux capacitor.... <<

    Of course!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Incredible BE bloat (2000+)

    I remember that Access 2000 (maybe 97) used to bloat terribly until they got a SP out for it.

    Is the database corrupt.

    Would you be better to create a new database and import all the tables into it.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    There is no evidence the db has been corrupted.

    I don't know whether or not they have tried importing to a new database (I believe it was discussed).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Incredible BE bloat (2000+)

    Are they up to date with their SP's?

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Incredible BE bloat (2000+)

    Yep, all up-to-date.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Incredible BE bloat (2000+)

    Clearing out a table and repopulating it causes a great deal of bloat, although that usually occurs where the query is run. However, the 2003 format includes the same pesky system table that causes bloat problems in 2002, MSysAccessObjects. It remembers every object ever in the database, and you CANNOT COMPACT that table! Decompile the database and then compact and repair, then create a NEW database and import all the non-system objects. The difference in the size will tell you roughly the impact of that single table. If that is the problem, you may need to fall back to the 2000 format, since in a BE, the 2002/2003 format doesn't really buy you anything. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Incredible BE bloat (2000+)

    A design suggestion Mark - move the table(s) that get cleared and repopulated to their own separate database and set it to do a compact on close. Then see if your main backend MDB continues to bloat. I would also revert the back-end to 2000 format - we've had nothing but trouble with the 2002/2003 format.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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