Couple of DB Maint Issues

[JimS]

My backend just went over a gig. Shouldn't be that big. I have several tables
that get recreated every week, and the space seems to get lost...So, I
compact and repair my A2003 db (A2000 format) every week using the starndard
utility. It's stopped working. Seems to work, but leaves a mysterious
"db1.mdf" behind and leaves the db untouched. Any ideas?

On the same subject, should I convert it to A2003 format? Does it matter?
How about the Front End?

Thanks!

 

[Norman Yuan]

db1.mdf? Is it possible a typo of "db1.ldb"? *.mdf is data file used by SQL
Server.

 

[JimS]

Typo...should be MDB
--
Jim

db1.mdf? Is it possible a typo of "db1.ldb"? *.mdf is data file used by SQL
Server.

 

[Klatuu]

I would convert both to 2003.
Have you downloaded and installed SP 3 for Office 2003?
If so, have you downloaded and applied the hotfix?

Deleting and recreating tables does contribute to bloat. A better design
would be each week to just delete the data from the tables, then reuse them.
It doesn' bloat as bad that way.

A compact/repair creates a copy of your database with the name db1.mdb (or
db2.mdb if db1 exists, etc). As it copies, is sorts out the junk. Once the
copy is complete, it renames you mdb, renames db1.mdb to your old name, then
deletes the original copy. If db1.mdb is there, that means it did not
complete successfully.
(I am great at stating the obvious, right). So the question is why. The
first thing I might look at is network stuff. Are there any space
restrictions on the folder you are using? Has some network jockey changed
permissions? You need read/write/create/delete permissions. Have you
watched it run to see if it throws any error? Not being a network expert, I
don't know if the network logging can catch this sort of thing, but you may
check with them to see if they see anything abnormal that has happened when
you do this.

 

[JimS]

Despite my host company's best efforts, I did download and install SP3, but I
don't know what hotfix you mean...

 

[JimS]

Ur not gonna believe this. I found the hotfix your referred to...though I was
a bit scared. I downloaded and installed it...not easy on my client's system.
Then I opened the backend. It hung for a LONG time. I rebooted, but before it
could reboot, it unstuck. I rebooted anyway.

When the client computer came back up again, I opened the BE and did a
compact an repair (I have a half dozen backups....) The BE went from 1 gig to
237MB! I had expected maybe a 25-35% reduction, but this was amazing, and as
far as I can tell, just perfect.

The front end dropped dramatically, as well.

Did this have to do with converting to A2003 format in addition to SP3 and
the Hotfix?

Do I need to make sure my clients all have SP3 and the hotfix?

 

[david]

Re-created? Sometimes you can work around this by doing updates
instead -- more work, but less bloat.

For example, I delete the old import database, copy the template file
to create a new import database, create the new table from the mainframe
export in the new import database, link to the new table, then update the
master table based on the differences between the master table and the new
file. At any one time, there are about 10000 cars on record, but only about
50 added and deleted per week. The file gets a compact and repair every
year or so.

(david)

 

[JimS]

Thank you, David.

The imports are huge (>70,000...) transaction lists from the corporate cloud
(whose margins I may not pierce....) There are no keys, many ways a trans
could be duplicate and still valid, etc. I am left with dumping the entire
set of trans out of the cloud each week and storing the whole thing.

Right now, I import a temp table to ensure I have the right set of columns,
etc. Bozo's are always changing the column sets. Then I delete all rows on
the production table and append the temp table into the production table and
delete the temp table.

Maybe I'll create a special db solely for the temp table, then delete the
whole db at the end...Could work....

What do you guys think?

 

[Klatuu]

Go to this site:
http://support.microsoft.com/kb/945674

 

[Klatuu]

I would recommend it. SP 3 fixed a few things, but introduced errors of its
own. The hotfix corrected the SP 3 errors. So if you are using them, you
could create a situation where it works on your computer, but not the
client's.