Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: any way to reduce db size after exceeding 2 GB?

    I have a database that has exceeded 2 GB in size. Is there any way to reduce the file size to a reasonable level without redesigning the database? I have tried several things such as the database splitter, deleting unused tables, queries, etc., but nothing seems to have worked.

    Thanks a lot!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: any way to reduce db size after exceeding 2 GB?

    Originally posted by dmbinshoreline
    I have a database that has exceeded 2 GB in size. Is there any way to reduce the file size to a reasonable level without redesigning the database? I have tried several things such as the database splitter, deleting unused tables, queries, etc., but nothing seems to have worked.

    Thanks a lot!
    Can we assume you've run a compact and repair?

  3. #3
    Join Date
    Apr 2004
    Posts
    12
    Thanks for the quick reply -- Yes, I ran the compact & repair and it made a copy of the database, but it ended up being the same size -- it's ~2.1 gb.

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    TRy the undocumented option /decompile. Make sure you back up your app as there is a chance of corruption.

    Originally posted by dmbinshoreline
    Thanks for the quick reply -- Yes, I ran the compact & repair and it made a copy of the database, but it ended up being the same size -- it's ~2.1 gb.

  5. #5
    Join Date
    Apr 2004
    Posts
    12
    I don't see the "decompile" option? Ran a search as well and came up with nothing. (i am using Access '00)

  6. #6
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Type the following in the Run window in start menu
    "C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile
    Make sure the path is correct.
    Then, pick your db. Once the db is open then compile, compact and then exit out. MAke sure you donot do anything else except, compile, compact. Open your db normally as you would and check if everything's working, then close. Your db size should come down significantly by now.

    good luck


    Originally posted by dmbinshoreline
    I don't see the "decompile" option? Ran a search as well and came up with nothing. (i am using Access '00)

  7. #7
    Join Date
    Apr 2004
    Posts
    12
    Originally posted by khan
    Type the following in the Run window in start menu
    "C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile
    Make sure the path is correct.
    Then, pick your db. Once the db is open then compile, compact and then exit out. MAke sure you donot do anything else except, compile, compact. Open your db normally as you would and check if everything's working, then close. Your db size should come down significantly by now.

    good luck
    I hope I did it right - I went into the DB, then had to go to a module, select Debug/Compile. Then I compacted and repaired (this is where it probably errored out - I had to hard kill it because it was "not responding"). I re-opened and no change. I wonder if the file is simply corrupted and I need to start over?

    thanks for all the input - it's much appreciated

  8. #8
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    did you /decompile it first ? Also whenever Access is performing a routine, it'll always say "Not responding" in the task list, so you should wait. For a huge db like your, its going to take some time.


    Originally posted by dmbinshoreline
    I hope I did it right - I went into the DB, then had to go to a module, select Debug/Compile. Then I compacted and repaired (this is where it probably errored out - I had to hard kill it because it was "not responding"). I re-opened and no change. I wonder if the file is simply corrupted and I need to start over?

    thanks for all the input - it's much appreciated

  9. #9
    Join Date
    Apr 2004
    Posts
    12
    Originally posted by khan
    did you /decompile it first ? Also whenever Access is performing a routine, it'll always say "Not responding" in the task list, so you should wait. For a huge db like your, its going to take some time.
    ok, i hope this is my last stupid question -- do i type exactly this into RUN?:

    "C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You say you have "copied" the database. Do you mean that you made a duplicate using, say, Windows Explorer ?

    Have you made a copy by creating a new, empty database and importing all the objects from the original database into the new database ?

    Also, why is it 2GB ? Do you really have that much data or is it ballooned from the actions of the DBEngine ?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i have never been tempted to put a db of this size in Access (2GB on a "dumb" A backend must be coffee-break slow), so this question may be naïve:

    what exactly is A's 2GB limit:

    2GB per application (in which case you are dead)

    or 2GB per back-end (in which case splitting application from data might help for a while)

    or 2GB per .MDB in the backend (in which case, splitting front/back and then further dividing the backend into multiple .MDB (one per table in the extreme) solves the problem (for a while)). if this last works, there is no real redesign needed, just a little fiddling with import & link to external data to smear the tables over several .mdb

    or...?

    2GB! are you certain that this is not a table-design question in disguise?


    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Smile

    just go to command.com, and do the C:\......\....mdb /decompile

    i think u mistook that for a module and debugging.

  13. #13
    Join Date
    Apr 2004
    Posts
    12
    Originally posted by PracticalProgram
    You say you have "copied" the database. Do you mean that you made a duplicate using, say, Windows Explorer ?

    Have you made a copy by creating a new, empty database and importing all the objects from the original database into the new database ?

    Also, why is it 2GB ? Do you really have that much data or is it ballooned from the actions of the DBEngine ?
    Not sure how the DB ever got to 2 GB because I just inherited it, but copying the tables over has done the trick! thanks for all the help

  14. #14
    Join Date
    Apr 2004
    Location
    in a mug..
    Posts
    15

    Re: any way to reduce db size after exceeding 2 GB?

    Originally posted by dmbinshoreline
    I have a database that has exceeded 2 GB in size. Is there any way to reduce the file size to a reasonable level without redesigning the database? I have tried several things such as the database splitter, deleting unused tables, queries, etc., but nothing seems to have worked.

    Thanks a lot!
    Is it possible archiving part of the data with a cut-off date for example?

    Same Db structure, but different file. Add something in your app that enables the user to access archived data.

    What sort of data is in that 2GB file?
    Not all who wander are lost, except maybe Waldo.

  15. #15
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    After you copied the database by copying the individual objects, what size was the database ?

    I have a feeling it was pretty small.

    I, personally, don't trust Access databases over, say, 50 MB.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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