473,392 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,392 software developers and data experts.

Exuberant database file size increase

Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain to
have to compact repair every few days. What is causing this massive file
size increase and is there any way to control it?

Thanks

Regards
Jan 3 '07 #1
5 4039
The action queries are causing the size increase. Access may not release the
previously used space until you compact the database.

The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;

Similarly, if you are relying on a unique index to weed out duplicates when
you run your INSERT, use a WHERE clause so there are fewer attempts.

Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...
>
I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from
a remote db. The mdb file size is normally around 80MB but since running
the vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain
to have to compact repair every few days. What is causing this massive
file size increase and is there any way to control it?

Thanks

Regards
Jan 3 '07 #2
Hi, John.
What is causing this massive file size increase
Most likely it's the many updates. If the new data doesn't fit into the space
currently allotted for the record, Jet has to make room for it. If there isn't
enough room in the current data page, then the current data page is split in
two, and the records distributed to each half accordingly. Now there's room in
one of the data pages for this new data in this record, but instead of one 4 KB
data page (for Jet 4.0), you now have two 4 KB data pages. Even if the data
pages aren't full (and even when there are no records in it), each data page is
4 KB. (Exception: Jet 2.0, 3.0, and 3.5 use 2 KB data pages to store data,
because they don't accommodate Unicode characters, like Jet 4.0 does.)
is there any way to control it?
Compact it more often.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...
Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After compact
and repair the size does get back to 80MB but it is still a pain to have to
compact repair every few days. What is causing this massive file size increase
and is there any way to control it?

Thanks

Regards

Jan 3 '07 #3
The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?

Thanks

Regards

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...
The action queries are causing the size increase. Access may not release
the previously used space until you compact the database.

The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;

Similarly, if you are relying on a unique index to weed out duplicates
when you run your INSERT, use a WHERE clause so there are fewer attempts.

Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...
>>
I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and
update queries on some table. The data to be inserted and updated is
coming from a remote db. The mdb file size is normally around 80MB but
since running the vb.net app the mdb file size grows to 1GB or so every
few days. After compact and repair the size does get back to 80MB but it
is still a pain to have to compact repair every few days. What is causing
this massive file size increase and is there any way to control it?

Thanks

Regards

Jan 3 '07 #4
Hi, John.
Is there any way to find out if no other user apart from me is using the db is
using the db so I can compact it safely?
The LDB Viewer will show all of the users currently in the database. You may
find a link to the LDB Viewer and other free diagnostic tools in the "Free
Microsoft Access Troubleshooting Tools" section on this Web page:

http://www.Access.QBuilt.com/html/links.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AMwrote in
message news:Op**************@TK2MSFTNGP03.phx.gbl...
Hi, John.
>What is causing this massive file size increase

Most likely it's the many updates. If the new data doesn't fit into the space
currently allotted for the record, Jet has to make room for it. If there
isn't enough room in the current data page, then the current data page is
split in two, and the records distributed to each half accordingly. Now
there's room in one of the data pages for this new data in this record, but
instead of one 4 KB data page (for Jet 4.0), you now have two 4 KB data pages.
Even if the data pages aren't full (and even when there are no records in it),
each data page is 4 KB. (Exception: Jet 2.0, 3.0, and 3.5 use 2 KB data
pages to store data, because they don't accommodate Unicode characters, like
Jet 4.0 does.)
>is there any way to control it?

Compact it more often.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:%2***************@TK2MSFTNGP04.phx.gbl...
>Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After compact
and repair the size does get back to 80MB but it is still a pain to have to
compact repair every few days. What is causing this massive file size
increase and is there any way to control it?

Thanks

Regards


Jan 3 '07 #5
John wrote:
The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?
I have used the code at

http://www.ffdba.com/downloads/Compa...ked_Tables.htm

successfully in the somehat distant past.

I believe some may have reported that it did not operate successfully
for them. The function CanBeOpenedExclusively may be helpful in
ascertaining whether or not the compact can be done.

If this were my db I would want to examine the .Net procedures that
added and modified data. The bloat you describe may be related to how
they do their job.

Jan 3 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: dchow | last post by:
Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down?
2
by: David | last post by:
Hello, I have several applications that maintain a Jet database via ODBC. There are perhaps two dozen tables, ony two of which have perhaps 10,000 records each. The rest are rather small. A...
30
by: Wes | last post by:
On a nightly basis, we shut the database down and do a file system backup. A short chronology of our database problem: 8/21 - count(*) of user tables succeeded (done once a week to get...
2
by: Mike | last post by:
I running MySQL locally and have the following dir setup C:\wamp\mysql\data\mike - This is where the tables for the database mike are stored C:\wamp\mysql\data - This is where I think the...
9
by: Adi | last post by:
Hello eveyone, I wanna ask a very simple question here (as it was quite disturbing me for a long time.) My problem is to read a file line by line. I've tried following implementations but still...
3
by: Limunski Magarac | last post by:
Hi all :) My apologies if I posted in the wrong groups, but I just jumped in MS SQL waters, so any guidance will be appreciated. What I'm trying to do is the following process: present...
6
by: sandyboy | last post by:
Hi, Please help me to solve this problem. My database size is 8 GB , 5GB of data file and 3GB of log file. I increased the size of datafile to 10GB and log file to 3.5 GB with 11GB and 4GB by 20...
1
by: nagmvs | last post by:
Hello I am unable to upload More than 200kb in to my server and also i can downlaod only less than 4MB file from my server .When i am searching in internet i found solution as u can...
0
by: dot.yet | last post by:
Hi Everyone, environment: DB2 9.5 WSE with FP 2a Solaris 10 u5 - 64-bit SMP - 2 Quad CPUS - total 8 cores 16 GB RAM SAMPLE database Solaris Containers
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.