DB Size

[Bee]

Hi,

What should be done when the size of a database keeps getting bigger and bigger?

Regards,
B

 

[freakazeud]

re:

Hi,
have you tried compact & repair (tools--database utilities). It is normal for a db to increase in size if it is holding more and more data...so is it increasing too much for no reason? What sort of data are you storing? You should not embed graphics or files within access itself. Is the db split into a frontend backend setup? Are you programmatically creating/deleting tables? What size are we talking about anyway (20MB...1GB...)?
HTH
Good luck

 

[Bee]

Hi,
have you tried compact & repair (tools--database utilities). It is normal for a db to increase in size if it is holding more and more data...so is it increasing too much for no reason? What sort of data are you storing? You should not embed graphics or files within access itself. Is the db split into a frontend backend setup? Are you programmatically creating/deleting tables? What size are we talking about anyway (20MB...1GB...)?
HTH
Good luck

No, my database is not increasing in size too much. I am using the compact & repair utility each time my database is closed. I was actually just wondering because one day it will reach a size where it will be too big and I am not sure what to do then!

What did you mean by "Are you programmatically creating/deleting tables?" ? I would like to know more about methods for managing large databases if you can share any please.

Thank you,
B

 

[freakazeud]

re:

So how much are we talking about?
As long as the db doesn't reach 2GB it should be fine. If it gets to close to that then you can try to implement workarounds by splitting your data into several backends and linking them together with one frontend. However, at some point it might just be better to upgrade to something more appropriate like Sql Server, Oracle, MySQL...! You could still keep an Access frontend if you wanted.
HTH
Good luck

 

[Bee]

So how much are we talking about?
As long as the db doesn't reach 2GB it should be fine. If it gets to close to that then you can try to implement workarounds by splitting your data into several backends and linking them together with one frontend. However, at some point it might just be better to upgrade to something more appropriate like Sql Server, Oracle, MySQL...! You could still keep an Access frontend if you wanted.
HTH
Good luck

So 2GB is the limit for Jet Engine. How about if my client kept using it until it got to that critical size and I were not there to split the backend. What could happen?

 

[freakazeud]

re:

It is 2GB minus the space needed for db system objects in Access 2002 and higher. Most likely the db will become unusable if it gets too close to that limit. Corruption could creep in as well.
HTH
Good luck

 

[Bee]

It is 2GB minus the space needed for db system objects in Access 2002 and higher. Most likely the db will become unusable if it gets too close to that limit. Corruption could creep in as well.
HTH
Good luck

Do I need to change any of the code in Access if I want to upgrade to MySQL for example and keep my Access frontend?

 

[freakazeud]

re:

Depends what you are doing in code...it is hard to see from here. Normally you would just link to the backend tables and keep the frontend (forms/reports/modules/queries...) within Access.
HTH
Good luck

 

[Bee]

Depends what you are doing in code...it is hard to see from here. Normally you would just link to the backend tables and keep the frontend (forms/reports/modules/queries...) within Access.
HTH
Good luck

Interesting stuff. Thanks.

 

[freakazeud]

re:

You're welcome.
Good luck on future projects!

 

[The_Doc_Man]

Bee - first things first. Databases grow. Fact of life. Growth occurs because until you compact a database, no data is ever deleted. Just "marked for delete." If you open a query, it creates a temporary list to point to the records that meet the query's selection criteria. When you close the DB, that list, being temporary, gets marked but not deleted, either.

When you say your database grows... look at it in "performance" terms. Does it double in size in a day, a week, a month? Guesstimate how long it will take to reach some size. Compact it and see how much you get back. You will eventually develop two numbers... a "real" growth rate - measured by looking at size post-compaction, and an "apparent" growth rate - measured by looking at size changes only between successive compactions. You would express either as Mb, Gb, or % of "base" size - where base size is chosen after some particular compaction that you choose as your reference point.

This information will tell you how often you need to compact the db and when it will "reallY" get too big to handle. Given that it is hard to find a modern system that DOESN'T have 60 Gb or more on a hard drive (unless you are shopping through some REALLY cheapie catalogs), you are unlikely to fill up the drive any time soon.

 

[Bee]

Bee - first things first. Databases grow. Fact of life. Growth occurs because until you compact a database, no data is ever deleted. Just "marked for delete." If you open a query, it creates a temporary list to point to the records that meet the query's selection criteria. When you close the DB, that list, being temporary, gets marked but not deleted, either.

When you say your database grows... look at it in "performance" terms. Does it double in size in a day, a week, a month? Guesstimate how long it will take to reach some size. Compact it and see how much you get back. You will eventually develop two numbers... a "real" growth rate - measured by looking at size post-compaction, and an "apparent" growth rate - measured by looking at size changes only between successive compactions. You would express either as Mb, Gb, or % of "base" size - where base size is chosen after some particular compaction that you choose as your reference point.

This information will tell you how often you need to compact the db and when it will "reallY" get too big to handle. Given that it is hard to find a modern system that DOESN'T have 60 Gb or more on a hard drive (unless you are shopping through some REALLY cheapie catalogs), you are unlikely to fill up the drive any time soon.

The Doc Man,

I am not worried about the physical size of my hard drive as it's large enough. I am more concerned about what data size the Jet engine can handle.

Also, I am setting my DB options to 'compact on close', is that alright or can it cause any problems do you think?

Thank you.
Regards,
B

 

[statsman]

Bee:

I assume any graphics or other OLE objects are linked rather than embedded.

Nothing increases the size of a database faster than embedded objects, pictures or graphics.

 

[Bee]

Bee:

I assume any graphics or other OLE objects are linked rather than embedded.

Nothing increases the size of a database faster than embedded objects, pictures or graphics.

statsman,

I don't store pictures ...etc in my database. My question was regarding the long term use of the DB will eventually make it large even though it's only storing data.

Regards,
B

 

[freakazeud]

re:

Also, I am setting my DB options to 'compact on close', is that alright or can it cause any problems do you think?

It is actually possible for this option to cause problems. The process of compacting a database is fairly involved. Access creates a new blank database...imports everything into it from the old one...deletes the old one and renames the new one to the name of the old one. While doing all this sometimes things just go wrong and you might end up with many new databases in your application folder which are all failed attempts of the compact option.
However, if this is a multi user environment...then the application should be split into a frontend/backend set up anyway. The frontend wouldn't really need any compacting since it never changes...unless you are running a lot of import routines or creating/deleting temp tables. But even if the frontend goes corrupt you can always just give the user a new copy of it. The backend is the important part. Since it sits on the server you should NEVER try to compact it over the network and only do so from the machine it is housed on. If you can do it there...then copy the file down to your local drive...compact it there...then move it back to the server and replace the old one. Of course all this could be automated with some scheduled task as well.
HTH
Good luck

 

[Bee]

It is actually possible for this option to cause problems. The process of compacting a database is fairly involved. Access creates a new blank database...imports everything into it from the old one...deletes the old one and renames the new one to the name of the old one. While doing all this sometimes things just go wrong and you might end up with many new databases in your application folder which are all failed attempts of the compact option.
However, if this is a multi user environment...then the application should be split into a frontend/backend set up anyway. The frontend wouldn't really need any compacting since it never changes...unless you are running a lot of import routines or creating/deleting temp tables. But even if the frontend goes corrupt you can always just give the user a new copy of it. The backend is the important part. Since it sits on the server you should NEVER try to compact it over the network and only do so from the machine it is housed on. If you can do it there...then copy the file down to your local drive...compact it there...then move it back to the server and replace the old one. Of course all this could be automated with some scheduled task as well.
HTH
Good luck

Will do, Thanks.

 

[freakazeud]

re:

You're welcome.
Good luck.

 

[GMLWORLDCHAMP!]

Freakazeud is right on...I ran into this issue in the past and currently have a database that exceeds this 2GB limit;

Key factors to keep in mind going forward with any Access mdb of this size:
File sharing use by users; my project is just that, I'm the only user. So far, it keeps opening but I'm sure there will come that time where the Jet simply can't handle opening the file anymore so I've documented each table (run the Documenter) tool and I have already prepared resources to convert this system into a more stable environment that can grow as much as it needs to...IBM's Universe database and accompanying CRM is GREAT!

Price tags for a mid-level Access database featuring ± 100,000 records and secondary processes = ± $150,000 to $175,000 depending of course on the vendor, company size, resources available to code, location, etc...

Back to factors, so if you are the only user, the file may last a bit longer but no guarantees once this limitation has been met...I'm looking forward to hearing back from some great users here like Mrs. Hartman because I trust her opinion 1000% ...check with her when you can.

Other factors, as Freakazeud said, the network environment is CRITICAL. All maintenance must be performed on that desktop, otherwise I guarantee you this database will crash and you will have to rebuild it; resetting all relationships, many table elements, etc...not to mention the probable corruption that will likely exist in all or part of your tables...I speak from grey hair growing experience!?#?$?!

If you have a database where the data expires at a given point that affects many records, like I did, I was able to create a duplicate functional database and archived all of the records as they became expired so the strain on the primary system was reduced significantly, which made it run quite a bit longer before it was ultimately upgraded to the Universe system! We got 6½ more months use out of it by doing this...

 

[Bee]

Freakazeud is right on...I ran into this issue in the past and currently have a database that exceeds this 2GB limit;

Key factors to keep in mind going forward with any Access mdb of this size:
File sharing use by users; my project is just that, I'm the only user. So far, it keeps opening but I'm sure there will come that time where the Jet simply can't handle opening the file anymore so I've documented each table (run the Documenter) tool and I have already prepared resources to convert this system into a more stable environment that can grow as much as it needs to...IBM's Universe database and accompanying CRM is GREAT!

Price tags for a mid-level Access database featuring ± 100,000 records and secondary processes = ± $150,000 to $175,000 depending of course on the vendor, company size, resources available to code, location, etc...

Back to factors, so if you are the only user, the file may last a bit longer but no guarantees once this limitation has been met...I'm looking forward to hearing back from some great users here like Mr. Hartman because I trust his opinion 1000% ...check with him when you can.

Other factors, as Freakazeud said, the network environment is CRITICAL. All maintenance must be performed on that desktop, otherwise I guarantee you this database will crash and you will have to rebuild it; resetting all relationships, many table elements, etc...not to mention the probable corruption that will likely exist in all or part of your tables...I speak from grey hair growing experience!?#?$?!

If you have a database where the data expires at a given point that affects many records, like I did, I was able to create a duplicate functional database and archived all of the records as they became expired so the strain on the primary system was reduced significantly, which made it run quite a bit longer before it was ultimately upgraded to the Universe system! We got 6½ more months use out of it by doing this...

Good points, thank you.

 

[boblarson]

from some great users here like Mr. Hartman

If you are talking about Pat Hartman, you have the wrong gender...I don't think she goes by Mr.