Further adventures in Jet database compaction

[Riley DeWiley]

I am using Jet 4.0 through OLEDB and C++.

My travails with compaction are well known to regular readers of this NG.
Many of my problems were solved by disabling record locking.

I am now confronted with two remaining mysteries, which I suspect are
related:
-------------------------------------------------------------
The Mystery of the Zombie Session (a horror story)

I have a code fragment that opens a simple test database, then compacts it
using IJetCompact. Usually works fine.
The sequence is:

...set up dbinit as a propset, nothing special ..

CDataSource db;
HRESULT hr = db.Open(sDBProviderName, (DBPROPSET*)&dbinit,
(ULONG)ELEMCOUNT(dbinit));

...set up for compaction ..

CComPtr<IJetCompact> spJetCompact = NULL;

CComPtr<IDBCreateSession> spSession = NULL;

ATLASSERT(db.m_spInit != NULL);

hr = db.m_spInit->QueryInterface(IID_IDBCreateSession, (void**)&spSession);

hr = spSession->QueryInterface( __uuidof(IJetCompact),
(void**)&spJetCompact);

CDBPropSet propset_compact[2];

propset_compact[0].SetGUID(DBPROPSET_DBINIT);

propset_compact[0].AddProperty(DBPROP_INIT_DATASOURCE, wszDest);

long x = GetJetEngineType( sDBProviderName, wszSrc );

propset_compact[1].SetGUID(DBPROPSET_JETOLEDB_DBINIT);

propset_compact[1].AddProperty(DBPROP_JETOLEDB_ENGINE, x);

propset_compact[1].AddProperty(DBPROP_JETOLEDB_DATABASEPASSWORD, sPWD);

CSession * psession = new CSession;
delete psession;

hr = spJetCompact->Compact(ELEMCOUNT(propset_compact), propset_compact);

So the basic sequence is:

Open DB ---> Compact

This works.

Now I do this:

Open DB ---> Open Session on DB ---> Compact.

This fails, not surprisingly, as all sessions need to be closed to compact.

Now, the mystery:

OpenDB ---> Open Session on DB ----> Close the same session ---> Compact

FAILS (E_FAIL)

What is going on??? The session is closed before compaction starts.

So that is Mystery #1.

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

The Galloping Bloat Mystery

I have code that watches my database to see when it has grown by X bytes (x
~= 250 MB usually)

When it crossed the magic line, it closes all sessions, and closes the DB,
and calls a function that opens the DB to compact it, then closes it again.
Compaction generally works and reduces database size (on the first try) by
about 30%. I am careful every time I open the DB, whether for compaction or
general use, to disable record locking, as this causes bloating.

After compacting, though, the database seems to grow massive again really
fast, forcing another compaction. This compaction is over quickly, and
reduces the database by some 80% very fast. The DB then starts growing very
quickly despite being stuffed with small amounts of data, and the process
repeats endlessly. I have walked away from computers in this state and come
back after a few hours to find the problem seems to have solved itself!

I suspect the Zombie Session is responsible - but can't be sure.

Anyone else encountered these nasties?

RDeW

[Kristina]

I have a client who is experiencing something similar enough that I
wanted to post this response, just in case we can find a common ground.

The program is composed of an Access 2003 front end and back end. They
are running it in a Terminal Server environment. We compacted their
data file last night after close of business and it was 144 MB. This
morning at 7:30 it was 1.75 GB. The growth seems to slow down once it
reaches 1.75 GB, but it has reached 2.05 GB before we discovered it and
compacted it.

We have many other clients that run this exact same program, and 28 of
the clients use Terminal Server. This is the only client that
experiences this problem. I know inserting images into a database can
cause huge bloat, but we do not do that.

If anyone has any ideas on what may be causing this, I'd be extremely
grateful to hear them. Thanks!

[Riley DeWiley]

Is record-level locking enabled when the DB is opened? That was the root
cause of much of my bloating.
Be aware that there are several places where record locking can be enabled;
one of them is a global default set in the registry, you can find helpful
information here :
http://ewbi.blogs.com/develops/2004/..._corrupti.html . Of course,
you should think about what kind of locking you need before you turn it off
....
I have found another cause of bloat but it is a much more complicated
scenario, contact me if you still need help.

RDeW

"Kristina" <kr**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I have a client who is experiencing something similar enough that I
wanted to post this response, just in case we can find a common ground.

The program is composed of an Access 2003 front end and back end. They
are running it in a Terminal Server environment. We compacted their
data file last night after close of business and it was 144 MB. This
morning at 7:30 it was 1.75 GB. The growth seems to slow down once it
reaches 1.75 GB, but it has reached 2.05 GB before we discovered it and
compacted it.

We have many other clients that run this exact same program, and 28 of
the clients use Terminal Server. This is the only client that
experiences this problem. I know inserting images into a database can
cause huge bloat, but we do not do that.

If anyone has any ideas on what may be causing this, I'd be extremely
grateful to hear them. Thanks!

[Kristina]

Thanks for your quick reply. I'm checking to see if record locking is
the cause of my problem. Thanks for your help!

Kristina

[Kristina]

Thanks for your quick reply. I'm checking to see if record locking is
the cause of my problem. Thanks for your help!

Kristina