Why did my Database size almost double after replication?

[Guest]

Why did my Database size almost double after replication? Can anyone explain
why?

Thanks

 

[Guest]

How big was it before then after? Going from 1 mb to 2 mb isn't no big thing.
Going from 100 mb to 200 mb, no so good.

 

[Guest]

Hi Eric,

Have you tried compacting the database (Tools > Database Utilities > Compact
and repair database) to see if some of the bloat is recovered?

Replicating a database adds several fields to each table. This is discussed
in the following KB article:

ACC2000: How to Make a Database Replicable
http://support.microsoft.com/?id=208774

You might also want to check out this paper:

ACC2000: Jet 4.0 Replication White Papers Available in MSDN Online Library
http://support.microsoft.com/?id=190766

Although both titles include "ACC2000", the information should be equally
valid for Access 2002 and 2003. I would also head on over to Michael Kaplan's
web site and read everything you can find on replication:

http://www.trigeminal.com/usenet/usenet.asp?1033


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Dirk Goldgar]

Why did my Database size almost double after replication? Can anyone
explain why?

Replication involves adding several fields to every table, as well as
creating a number of new system tables. So it will certainly increase
the size of the database; by what proportion must depend on the
original size, number of tables, and number of fields. Did you compact
your database after replicating? It may well be that the replication
process used some temporary space that a compact would reclaim, though I
don't know that to be a fact.

 

[Guest]

I have compacted the db before and after i replicated it. The size went from
27 meg to 52 meg. It is not a big deal but I am curious as to Why.

Thanks

 

[aaron.kempf]

because using _ACCESS_ for replication is kindof like banging your
pistol to bang yourself in the ass.

yeah; it might be easy; yeah it might be fun

but BANG one day you've lost data and there's no turning back.

REPLICATION IS A SERIOUS DATABASE TECHNOLOGY.
USE A SERIOUS DATABASE!

 

[Guest]

If your database is properly normalized (ie. long & skinny with lots of
records but without lots of columns), you will likely see a much larger
increase in size vs. replicating a fat "Access spreadsheet" with multivalued
fields (ie. JanSales, FebSales, MarSales....). The reason for this is that
replication adds four additional fields to each table, in addtion to the
hidden system table that Dirk mentioned. Each of these fields gets data
automatically added to it.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

You might also want to check out this paper:

ACC2000: Jet 4.0 Replication White Papers Available in MSDN Online
Library
http://support.microsoft.com/?id=190766

A must-read is the replication FAQ, too, which corrects a lot of the
marketing-speak in the white paper.

 

[David W. Fenton]

I have compacted the db before and after i replicated it. The
size went from 27 meg to 52 meg. It is not a big deal but I am
curious as to Why.

Did you replicate only the tables, or did you replicate
forms/reports/etc. as well?

 

[David W. Fenton]

Replication involves adding several fields to every table, as well
as creating a number of new system tables. So it will certainly
increase the size of the database;

If you're properly replicating only tables, it won't increase the
size *that* much. Just testing, I converted a 78MB A97 data file to
A2K, then replicated it. The A2K file was 94MBs after compacting,
and when replicated, it was, to my shock, 242MBs.

Jet 3.5 was not like this at all. I'm not sure why it's so much
bigger.

It would seem to me from this experiment that 2X increase is about
normal.

 

[Dirk Goldgar]

If you're properly replicating only tables, it won't increase the
size *that* much. Just testing, I converted a 78MB A97 data file to
A2K, then replicated it. The A2K file was 94MBs after compacting,
and when replicated, it was, to my shock, 242MBs.

Jet 3.5 was not like this at all. I'm not sure why it's so much
bigger.

It would seem to me from this experiment that 2X increase is about
normal.

I haven't done replication with Access 97. So you're saying that Jet 4
replication gives a much greater size increase than Jet 3.5 replication?
That's hard to explain, unless it has something to do with uncompressed
unicode. Interesting.

 

[David W. Fenton]

If your database is properly normalized (ie. long & skinny with
lots of records but without lots of columns), you will likely see
a much larger increase in size vs. replicating a fat "Access
spreadsheet" with multivalued fields (ie. JanSales, FebSales,
MarSales....). The reason for this is that replication adds four
additional fields to each table, in addtion to the hidden system
table that Dirk mentioned. Each of these fields gets data
automatically added to it.

But most of those fields are rather small in terms of data types.
The lineage fields can grow large, but won't be when you create your
first replica. And you have only one of those for each record,
unless you have a bunch of memo fields (one lineage field for each
memo).

I don't know why Jet 4 increases database size so much when
replicated. It's definitely a much bigger difference than was the
case with Jet 3.5.

 

[aaron.kempf]

desn't moving from 97 to 2000 give you 'features' like unicode
compression and double-byte character storage?

uih... can you guys not make the mental jump from double-byte
character storage to a doubling in size from 97 to 2000?

 

[David W. Fenton]

I haven't done replication with Access 97. So you're saying that
Jet 4 replication gives a much greater size increase than Jet 3.5
replication? That's hard to explain, unless it has something to do
with uncompressed unicode. Interesting.

Well, plain non-replicated apps increase about 50% in size when
converted from A97 to A2K, and then often bloat a lot more with use
than A97 ever did, so adding another 50% for replication overhead
doesn't seem that out of the way to me.

 

[aaron.kempf]

I fully agree

the moral of the story is 'JUST USE ADP'

they _ROCK_ with SQL 2005 support now in Access 2007