Access DB back-end File bloating

[jameso321]

Hi,


We run an MS Access 2000 DB with about 15 users.


It is on a Win 2000 Server (SP4) machine and runs through Citrix
Metaframe Presentation Server 3.0.

--------------------------

The backend files size is usually about 30 meg or so and recelty it
has been bloating on random occasions to about 700-800 megs.


The DB developer is stumped as is the Server Tech.

I provide Client-side tech and light server support for them.

-------------------------


This is solved with a repair and compact when it occurs, but it is
happening about once per week now instead of once every 3 months or
so.




Does anyone have any ideas regarding the causes of this type of thing?


Thanks,

Jameso321

 

[Albert D.Kallal]

First, in access 2000, a good number of serious bloating problems where
fixed, and thus you have to install the service packs for office 2000, and
also for JET.

Assuming that you installed the many bug fixes for office and jet via the
service packs, then the next thing to check is if any new code, or a change
in some type of import, or report is being run that uses temp data.

So, usually, bloat is result of some code, or routines that creates temp
data, or uses a temp table for processing. We all know that any temp table
used in code will cause the bloat.

You can do some further reading here:
http://www.granite.ab.ca/access/bloatfe.htm

the above talks about the front end, but much also applies to the back
end...

 

[David W. Fenton]

We run an MS Access 2000 DB with about 15 users.

It is on a Win 2000 Server (SP4) machine and runs through Citrix
Metaframe Presentation Server 3.0.

--------------------------

The backend files size is usually about 30 meg or so and recelty
it has been bloating on random occasions to about 700-800 megs.

The DB developer is stumped as is the Server Tech.

I provide Client-side tech and light server support for them.

-------------------------

This is solved with a repair and compact when it occurs, but it is
happening about once per week now instead of once every 3 months
or so.

Does anyone have any ideas regarding the causes of this type of
thing?

As Albert suggested, make sure all users are updated to a decent
version of Access (this means applying Office SR1 or later; the
reason one might choose to not implement SP3 is that it includes the
Draconian Outlook "security" patch that disables nearly all useful
attachments), and each workstation must be patched to at least Jet 4
SP6 (the current version is 8; 7 was buggy and is not available; 6
is sufficient, though 8 patches some code execution
vulnerabilities).

But the most likely reason for bloat is bad application design.

If the application is adding and deleting records from the back end,
this will cause lots of bloat. This is a common mistake by rookie
programmers, using a temp table in the back end, adding records to a
workset, then deleting them.

Any temp tables should probably be in a separate temp.mdb on the
individual workstation, rather than on the server.

 

[jameso321]

First, in access 2000, a good number of serious bloating problems
where
fixed, and thus you have to install the service packs for office 2000,
and
also for JET.

Assuming that you installed the many bug fixes for office and jet via
the
service packs, then the next thing to check is if any new code, or a
change
in some type of import, or report is being run that uses temp data.

So, usually, bloat is result of some code, or routines that creates
temp
data, or uses a temp table for processing. We all know that any temp
table
used in code will cause the bloat.

You can do some further reading here:
http://www.granite.ab.ca/access/bloatfe.htm

the above talks about the front end, but much also applies to the back

end...

Thanks for responding. Windows 2k Server and Office 2k on that server
are current with respect to service packs and normal updates.

I have passed this information to the DB developer, but he insists
that the problem is external to the DB.

Do you have anything to say regarding causes that are not DB related?

Keep in mind this is all run on Citrix Metaframe Presentation Server
3.0 and the whole shabang runs on the server itselfl; the front ends
and back end. Each user has their own front end.


Thanks

 

[jameso321]

As Albert suggested, make sure all users are updated to a
decent
version of Access (this means applying Office SR1 or later;
the
reason one might choose to not implement SP3 is that it
includes the
Draconian Outlook "security" patch that disables nearly all
useful
attachments), and each workstation must be patched to at least
Jet 4
SP6 (the current version is 8; 7 was buggy and is not
available; 6
is sufficient, though 8 patches some code execution
vulnerabilities).

But the most likely reason for bloat is bad application
design.

If the application is adding and deleting records from the
back end,
this will cause lots of bloat. This is a common mistake by
rookie
programmers, using a temp table in the back end, adding
records to a
workset, then deleting them.

Any temp tables should probably be in a separate temp.mdb on
the
individual workstation, rather than on the server.

--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc

Thanks for replying.

Let’s talk about the magnitude of bloating. I keep an eye on the mdb
file pretty much all day now.

It will go from 30 megs to 600 megs in an instant, then up to 975
megs.

This is not gradual.


Thanks,

 

[jameso321]

As Albert suggested, make sure all users are updated to a decent
version of Access (this means applying Office SR1 or later; the
reason one might choose to not implement SP3 is that it includes the
Draconian Outlook "security" patch that disables nearly all useful
attachments), and each workstation must be patched to at least Jet 4
SP6 (the current version is 8; 7 was buggy and is not available; 6
is sufficient, though 8 patches some code execution
vulnerabilities).

But the most likely reason for bloat is bad application design.

If the application is adding and deleting records from the back end,
this will cause lots of bloat. This is a common mistake by rookie
programmers, using a temp table in the back end, adding records to a
workset, then deleting them.

Any temp tables should probably be in a separate temp.mdb on the
individual workstation, rather than on the server.

--------------------------------------

Thanks for replying,

The good news is patching and such is easy for me as we use an
application server (Citrix Metaframe Presentation Server 3.0s)

I only have to deal with one copy of Office, and that copy is fully
patched.

I am passing your comments to the DB Programmer.


Thanks again,

 

[Albert D.Kallal]

Keep in mind this is all run on Citrix Metaframe Presentation Server

3.0 and the whole shabang runs on the server itselfl; the front ends
and back end. Each user has their own front end.

Yes, clear on the above....

The interesting issue here is that 'all of a sudden' seems to be the strange
part. That seems strange if no updates or anything was done, and the
behavior started to change. I have to think somthing happend here.....

 

[David W. Fenton]

Let’s talk about the magnitude of bloating. I keep an eye on the
mdb file pretty much all day now.

It will go from 30 megs to 600 megs in an instant, then up to 975
megs.

This is not gradual.

Well, if a user initiates an operation that appends thousands of
records to a temp table and then those get deleted, and shortly
thereafter another user does the same thing, you could easily see
this kind of bloat. It depends on the size of the recordsets
involved.

Also, since it's a Terminal Server setup, I wonder if it's a proper
split architecture, with data tables in a shared MDB and the
forms/reports, etc. in a separate MDB for each user using the
application? Either way, split or unsplit, if multiple users are
opening the same MDB file, you're going to see massive bloat in the
MDB that has the forms/reports, etc.

If your developer did not split the database or is not giving each
user their own copy of the front end MDB (the one with the
forms/reports), then you're not really dealing with a professional
Access developer.

See http://www.granite.ab.ca/access/splitapp/index.htm for an
explanation of why. There's also a solution there for distributing
the front end to end users transparently,
http://www.granite.ab.ca/access/autofe.htm . This is something I've
been using in various projects for several years now, and it works
just great.

 

[David W. Fenton]

Yes, clear on the above....

The interesting issue here is that 'all of a sudden' seems to be
the strange part. That seems strange if no updates or anything was
done, and the behavior started to change. I have to think somthing
happend here.....

It sounds like some kind of patch or hotfix was applied to the
server.

I've told this story a gazillion times, but it teaches a good
lesson, so I'll tell it again:

Back in winter of 1999 a client of mine was was using a nice app of
mine that had a replicated back end to support their two offices and
two off-site users. Everything regarding replication had been
working smoothly for about 9 months when suddenly one day at 2:30pm
the back end in the NYC office reported itself corrupted. When we
compacted a copy, it came up having lost replicability.

We did troubleshooting on this for quite some time, recovering
manually the data that had been entered since the last successful
synchronization (it was on a schedule for several times a day, so it
wasn't a huge number of updates; and all the data tables had time
stamps in them, so I know what had been updated when).

Eventually, we determined that a hotfix that had been applied to
Exchange Server (to fix the problem with Exchange not shutting down
when the server was shut down, and causing the server to shut down
dirty) about 15 minutes before the first corruption. Since the
machine should never have had Exchange Server running on it in the
first place, we backed out the hotfix.

The problems vanished and never returned.

Now, this was a case where two interesting things happened:

1. a patch to an unrelated piece of software caused corruption of an
Access database, and

2. the consultants involved were too stupid to ask if Exchange
Server was in use. Had they done so, they could have saved everybody
a lot of grief by simply shutting down Exchange Server instead of
applying a hotfix for a program that wasn't even in use.

So, I'd try to determine exactly when the problem first started
occurring, then go back to the event logs to see if there's anything
there. Hopefully they are configured to not overwrite too quickly
and you could find out what happened from them.

 

[jameso321]

 >> Keep in mind this is all run on Citrix Metaframe
Presentation
 >> Server 3.0 and the whole shabang runs on the server
itselfl; the
 >> front ends and back end. Each user has their own
front end.

It sounds like some kind of patch or hotfix was applied to the
server.

I've told this story a gazillion times, but it teaches a good
lesson, so I'll tell it again:

Back in winter of 1999 a client of mine was was using a nice
app of
mine that had a replicated back end to support their two
offices and
two off-site users. Everything regarding replication had been
working smoothly for about 9 months when suddenly one day at
2:30pm
the back end in the NYC office reported itself corrupted. When
we
compacted a copy, it came up having lost replicability.

We did troubleshooting on this for quite some time, recovering
manually the data that had been entered since the last
successful
synchronization (it was on a schedule for several times a day,
so it
wasn't a huge number of updates; and all the data tables had
time
stamps in them, so I know what had been updated when).

Eventually, we determined that a hotfix that had been applied
to
Exchange Server (to fix the problem with Exchange not shutting
down
when the server was shut down, and causing the server to shut
down
dirty) about 15 minutes before the first corruption. Since the
machine should never have had Exchange Server running on it in
the
first place, we backed out the hotfix.

The problems vanished and never returned.

Now, this was a case where two interesting things happened:

1. a patch to an unrelated piece of software caused corruption
of an
Access database, and

2. the consultants involved were too stupid to ask if Exchange
Server was in use. Had they done so, they could have saved
everybody
a lot of grief by simply shutting down Exchange Server instead
of
applying a hotfix for a program that wasn't even in use.

So, I'd try to determine exactly when the problem first
started
occurring, then go back to the event logs to see if there's
anything
there. Hopefully they are configured to not overwrite too
quickly
and you could find out what happened from them.

--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc

Each user has their own front.

The frequency of the problem in the last month or so is -

File Size - Date - Time

696,368 25 May 15:32
941,668 27 May 16:03
625,604 13 Jun 20:48
974,596 21 Jun 12:48


-----------------

Normal file size is 27,984 or so.

I had seen this occur at least two times in the last 12 months besides
this past 30 days.

-------------------

I know what you are saying about updates and hotfixes, its too bad
that the timeline doesnt match up with any server work.



Thanks everyone for your input,


Jameso321

 

[aaron.kempf]

more importantly.. screw MDB with a 10-foot pole

use ADP -- Access Data Projects

they are a LOT more reliable.

 

[Larry Linson]

more importantly.. screw MDB with a 10-foot pole

use ADP -- Access Data Projects

they are a LOT more reliable.

Any gain in reliability would come from using SQL Server as the datastore,
which can be done perfectly well with an MDB/MDE front end. I have used both
MDB/MDE and ADP as clients to Microsoft SQL Server. I had no "disasters"
with either, but did not see any advantage of ADP over MDB in reliability
nor in performance. I did see some restrictions that would make me question
why it would be worth anyone's time and effort to learn the "tricks of the
ADP trade" when they already knew the "tricks of the MDB/MDE trade".

In any case, wasn't it you complaining that you'd heard that ADPs were going
to be "deprecated" in the next version? (I have not, by the way, seen an
official announcement of that from Microsoft.) It would seem very poor
advice to recommend something that you believe is to be deprecated soon.

Larry Linson
Microsoft Access MVP

 

[aaron.kempf]

i dont believe those now; they were rumors effectively

ADP is _MUCH_ better from a performance and stability standpoint.. and
it's much more flexible.. i mean-- keeping all your logic in stored
procs and views-- it is much more powerful than MDB querydefs

 

[aaron.kempf]

i dont believe those now; they were rumors effectively

ADP is _MUCH_ better from a performance and stability standpoint.. and
it's much more flexible.. i mean-- keeping all your logic in stored
procs and views-- it is much more powerful than MDB querydefs

and just for the record; using MDB as a front end to SQL Server is
_TOTALLY_ inefficient and difficult.

you can write 1/3 the amount of code and get a more stable.. more
scalable solution using SQL Server as the backend.

MDB/ODBC is a friggin PAIN

 

[David W. Fenton]

i dont believe those now; they were rumors effectively

ADP is _MUCH_ better from a performance and stability standpoint..
and it's much more flexible.. i mean-- keeping all your logic in
stored procs and views-- it is much more powerful than MDB
querydefs

Are you really as stupid as these two posts make you sound?

ADPs work only with SQL Server, so by saying "switch to ADP" you're
saying "switch the back end to SQL Server and then rewrite your
front end as an ADP."

Chances are that just switching the back end away from Jet would
solve the bloating problem, since it's by definition a Jet issue,
and was in the BACK END, not the front ends.

In any event, the kind of bloat described is not normal for Jet
under any circumstances I know, so the problem can be solved without
requiring the added overhead and administrative headache of moving
the back end to SQL Server.

And the idea that anyone would migrate a working MDB to an ADP just
boggles the mind -- how stupid can you get? Not even Lyle recommends
that course of action.

 

[Larry Linson]

i dont believe those now; they
were rumors effectively

IIRC, you reported that's what you were told by Microsoft tech support. Of
course, it is possible that they didn't know what they were talking about.
Perhaps, if they did, they violated their non-disclosure-agreements.

ADP is _MUCH_ better from a
performance and stability standpoint..
and it's much more flexible.. i mean--
keeping all your logic in stored
procs and views-- it is much more
powerful than MDB querydefs

Shouting "_MUCH_" does not, of course, make it so. You also seem to draw no
distinction between the front end and the back end. Anyone with significant
experience knows you cannot "keep _all_ your logic" in stored procedures and
you can't keep _any_ logic in a view that you couldn't keep in a saved
Query.

and just for the record; using MDB
as a front end to SQL Server is
_TOTALLY_ inefficient and difficult.

I've been doing that since Access version 2.0 -- to Microsoft SQL Server,
Sybase SQL Server, and Informix and I did not find it as you describe. I
found it to be perfectly adequate in efficiency and performance, and not
difficult at all to those who knew what they were doing.

you can write 1/3 the amount of code
and get a more stable.. more
scalable solution using SQL Server
as the backend.

That claim just does not match my experience, and, as I've stated, you don't
need ADP to use Microsoft SQL Server as the backend. Try using Informix or
Sybase as the backend database with ADP and tell us what you found about
ease and efficiency. You may be able to use a little less code if you move a
lot of logic to stored procedures and triggers and don't count those lines.

MDB/ODBC is a friggin PAIN

I tend to agree there is a _pain_ in this thread, but it isn't MDB/ODBC.

Larry Linson

 

[Trevor Best]

MDB/ODBC is a friggin PAIN

You must be doing it wrong then. I've used this method for the last 6
years, our mainstream product is a procurement/materials management
system that goes from MTO through all apects of procurement
(req/enquiry/bid evaluation/PO/expediting/shipping/engineering and
vendor document control) and then through to material control
(receiving/issuing/returns) and then matches invoices to boot (next
version will surely make the tea as well).

So no small system then, I don't feel this friggin pain.

 

[jameso321]

Are you really as stupid as these two posts make you sound?

ADPs work only with SQL Server, so by saying "switch to ADP"
you're
saying "switch the back end to SQL Server and then rewrite
your
front end as an ADP."

Chances are that just switching the back end away from Jet
would
solve the bloating problem, since it's by definition a Jet
issue,
and was in the BACK END, not the front ends.

In any event, the kind of bloat described is not normal for
Jet
under any circumstances I know, so the problem can be solved
without
requiring the added overhead and administrative headache of
moving
the back end to SQL Server.

And the idea that anyone would migrate a working MDB to an ADP
just
boggles the mind -- how stupid can you get? Not even Lyle
recommends
that course of action.

--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc

Thanks everyone.

 

[jameso321]

First, in access 2000, a good number of serious bloating problems
where
fixed, and thus you have to install the service packs for office 2000,
and
also for JET.

Assuming that you installed the many bug fixes for office and jet via
the
service packs, then the next thing to check is if any new code, or a
change
in some type of import, or report is being run that uses temp data.

So, usually, bloat is result of some code, or routines that creates
temp
data, or uses a temp table for processing. We all know that any temp
table
used in code will cause the bloat.

You can do some further reading here:
http://www.granite.ab.ca/access/bloatfe.htm

the above talks about the front end, but much also applies to the back

end...

------------------------


Thanks for all the help here.

One last question.

The server has Norton AV Corp version 6 on it. The realtime scanner
is on and I have recently excluded the db realted file extensions from
realtime scanning.

Has anyone ever seen Bloat from something related to the NAV realtime
scanner?


Thanks

 

[Tony Toews]

The server has Norton AV Corp version 6 on it. The realtime scanner
is on and I have recently excluded the db realted file extensions from
realtime scanning.

Has anyone ever seen Bloat from something related to the NAV realtime
scanner?

There shouldn't be any bloat by an antivirus scanner as it should be
opening files read only. Performance slowdown yes but bloating no.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm