Compact and Repair Advice

[Keven Denen]

I'm attempting to compact and repair a fairly large (approximately
900MB), non-split, replicated database (yes, non-split meaning they
are replicating the front end objects, this is an issue I'm taking up
with the owner of the database). When I compact and repair it, I get
the "Not enough space on temporary disk." error message. The hard
drive I'm using has over 200 GB of empty space so it's definately not
a hard drive issue. Googling the error message, I found all sorts of
advice telling me to compact and repair the database to resolve this,
I couldn't find anything that referred to this error happening while
compacting.

I'm pretty sure that a large part of the size is replication data (the
tombstone table has over 2 million records). The retention period on
the database was set to the default 1000 days and the database is
about 3 years old. I set the retention period to 30 days as the
company using this database syncs on a pretty regular basis (usually
daily). Now that I've changed the retention period and synced, I'm
trying to compact and repair to clear out some of the old replication
records and shrink this database somewhat. I went through this process
with a test database that wasn't quite as large and everything worked
and I shrunk the database to about 70% of the original size. I'm
hoping to get this larger database to do the same thing, but I can't
get it to compact.

Any tips on getting this thing to compact and repair?

 

[S.Clark]

I don't know Jack about replication...

Can you:
Create a new database file.
Import all objects from the current.
(As it writes the objects, it will typically remove the fluff.)

 

[Daryl S]

Keven -

You can try opening a new database and importing all the objects into it.
See if that helps.

 

[Keven Denen]

I don't know Jack about replication...

Can you:
Create a new database file.
Import all objects from the current.
(As it writes the objects, it will typically remove the fluff.)







- Show quoted text -

Unfortunately, importing the objects into a new database causes them
to lose their replicable status so this won't work.

Some additional information that may or may not be helpful. I adjusted
the retention period to see if I could find a point where it will
compact. It seems that any period over 146 days allows it to compact,
but doesn't remove any of the tombstone data. Any value under that
causes the error.

Keven

 

[David W. Fenton]

:

Any tips on getting this thing to compact and repair?

JetComp.exe is the only recommendation I can think of.

Also, if your MSysTombstone table is taking up 70% of the space,
then you have a major design error. You just shouldn't be deleting
that many records, particularly if you take note of the fact that
the deleted data is not stored in that table, just the meta-data
about the record that is necessary to propagate the deletion. If
there's a conflict, the record in the replica with the conflict
still has the data of the deleted record, so that's why it doesn't
need to be stored in MSysTombstone.

Now, given that MSysTombstone is less than a half dozen fields
(though one is a BLOB field, but not a large one, as it's only the
lineage tracking), the fact that it's taking up so much room
indicates to me that you (or the original programmer) has made
record deletion a regular part of your application's daily
operations. This is an error -- data that gets deleted should not be
replicated because it's temporary data.

If that data needs to be seen in all replicas, then it shouldn't be
deleted at all. Instead, it should be retained and just flagged as
deleted. Then the user interface objects would need to be revised to
filter out the records flagged as deleted. This is actually a
perfectly normal way of handling deletions even in non-replicated
apps.

It may also be that replicating the front end leads to the massive
number of records in MSysTombstone. If so, that's yet another reason
to get it properly split ASAP.

 

[David W. Fenton]

You can try opening a new database and importing all the objects
into it. See if that helps.

This is not valid advice for an unsplit replicated application.

 

[David W. Fenton]

:

Some additional information that may or may not be helpful. I
adjusted the retention period to see if I could find a point where
it will compact. It seems that any period over 146 days allows it
to compact, but doesn't remove any of the tombstone data. Any
value under that causes the error.

Have you tried on a temporary copy deleting the front-end objects
and seeing if it would compact then?

Worth a try, and if it works, it's a pretty good indicator that you
need to get it split as quickly as possible.

 

[Keven Denen]

Have you tried on a temporary copy deleting the front-end objects
and seeing if it would compact then?

Worth a try, and if it works, it's a pretty good indicator that you
need to get it split as quickly as possible.

I've inherited this database from a past programmer so I'm dealing
with his deficiencies. I've discussed the issue of replicating front
end objects with the company and we plan on changing it. After you
mentioned the issue of deletions in one of the last posts you made to
my other question about replication, I looked into why all these
deletions occur and it mostly comes from a temporary table that one of
the forms uses. It creates the table, fills it with records and
deletes it every time the user re-opens the form. I'll be redesigning
this part as well.

I think the best bet is to unreplicate everything, split it and
redesign the problem areas.

Thanks again for all your advice David.

Keven

 

[John W. Vinson]

. It creates the table, fills it with records and
deletes it every time the user re-opens the form.

One idea to consider is to create the temp table - not in your production
database - but in a "scratchpad" database created on demand for this purpose
only.

 

[David W. Fenton]

I've inherited this database from a past programmer so I'm dealing
with his deficiencies. I've discussed the issue of replicating
front end objects with the company and we plan on changing it.

I keep saying, sooner rather than later...

After you mentioned the issue of deletions in one of the last
posts you made to my other question about replication, I looked
into why all these deletions occur and it mostly comes from a
temporary table that one of the forms uses. It creates the table,
fills it with records and deletes it every time the user re-opens
the form. I'll be redesigning this part as well.

A hard and fast rule for replicated databases is NEVER, NEVER EVER
replicated any temp tables, under any circumstances. I learned this
lesson in the very first replicated application I ever created back
in 1997.

To fix it, move that table to a separate MDB that is stored in the
same folder as the application front end. You can easily
automatically relink to that table with this code (which I wrote for
an app just this past week):

Public Function CheckTmpLinks(strBackEndFileName As String) _
As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strPath As String
Dim strCurrentPath As String

strCurrentPath = CurrentProject.Path
Set db = CurrentDB
For Each tdf In db.TableDefs
If InStr(tdf.Connect, strBackEndFileName) <> 0 Then
strPath = GetPath(Mid(tdf.Connect, 11))
If strPath <> strCurrentPath Then
tdf.Connect = ";Database=" + strCurrentPath _
& "\" & strBackEndFileName
tdf.RefreshLink
Debug.Print "Reconnected to " & tdf.Name
End If
End If
CheckTmpLinks = True
Next tdf
Set tdf = Nothing
Set db = Nothing
End Function

As long as you distribute your temporary MDB along with the new
front end, it will transparently eliminate the problem.

I think the best bet is to unreplicate everything, split it and
redesign the problem areas.

No need to unreplicate -- just import all the non-table objects into
an empty MDB, and delete them from the Design Master.

 

[David W. Fenton]

One idea to consider is to create the temp table - not in your
production database - but in a "scratchpad" database created on
demand for this purpose only.

FWIW, while I certainly have the programming chops to do this, I've
never felt it was worth the time. I just distribute the empty tmp
database along with any new front end -- it becomes part of the
version distribution (I learned a long time ago to just distribute
both and then never have to worry if there'd been changes that
required distributing a new version of the temp file).

 

[John W. Vinson]

FWIW, while I certainly have the programming chops to do this, I've
never felt it was worth the time. I just distribute the empty tmp
database along with any new front end -- it becomes part of the
version distribution (I learned a long time ago to just distribute
both and then never have to worry if there'd been changes that
required distributing a new version of the temp file).

Reasonable. The advantage I see to creating and KILLing the scratch database
is that it will never take up more space than is needed; if you distribute it,
you'll need to worry about compacting *IT* too, or replacing it periodically.

 

[Tony Toews [MVP]]

One idea to consider is to create the temp table - not in your production
database - but in a "scratchpad" database created on demand for this purpose
only.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

Reasonable. The advantage I see to creating and KILLing the
scratch database is that it will never take up more space than is
needed; if you distribute it, you'll need to worry about
compacting *IT* too, or replacing it periodically.

I have programmed an app or two to copy the empty template from the
server to the user's workstation on application load (thus avoiding
any need for a compact), but I haven't done it very often. Temp
databases are disposable and hard drive space is nearly unlimited,
so it's not something I give the slightest thought to.

On the other hand, most of my temp databases are not supporting
anything terribly computationally intensive, nor for huge datasets.
Mostly I use them for these purposes:

1. staging table(s) for overly complex reports that don't run well
with pure SQL recordsources.

2. export tables for driving mailings and the like.

3. import tables for recurring data import processes.

4. two-column tagging tables to support select lists with
checkboxes.

None of those things has ever cause any performance issues in any of
the apps I've every used them in, and none of those apps had any
provision for compacting the temp file, and precious few of them
replaced the file with the empty template.

Perhaps I've been lucky, but I started out using the empty template
method, and then even let that fall by the wayside.

 

[chenzehua]

I keep saying, sooner rather than later...


A hard and fast rule for replicated databases is NEVER, NEVER EVER
replicated any temp tables, under any circumstances. I learned this
lesson in the very first replicated application I ever created back
in 1997.

To fix it, move that table to a separate MDB that is stored in the
same folder as the application front end. You can easily
automatically relink to that table with this code (which I wrote for
an app just this past week):

Public Function CheckTmpLinks(strBackEndFileName As String) _
As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strPath As String
Dim strCurrentPath As String

strCurrentPath = CurrentProject.Path
Set db = CurrentDB
For Each tdf In db.TableDefs
If InStr(tdf.Connect, strBackEndFileName) <> 0 Then
strPath = GetPath(Mid(tdf.Connect, 11))
If strPath <> strCurrentPath Then
tdf.Connect = ";Database=" + strCurrentPath _
& "\" & strBackEndFileName
tdf.RefreshLink
Debug.Print "Reconnected to " & tdf.Name
End If
End If
CheckTmpLinks = True
Next tdf
Set tdf = Nothing
Set db = Nothing
End Function

As long as you distribute your temporary MDB along with the new
front end, it will transparently eliminate the problem.


No need to unreplicate -- just import all the non-table objects into
an empty MDB, and delete them from the Design Master.