Automate Compact/Repair (1 Viewer)

cajulien

New member
Local time
Today, 05:52
Joined
Feb 3, 2020
Messages
25
they also shouldn't be compacting shared databases either and I'm not sure how you will control that.
What do you mean by "shared" in this case? Shared across front-ends? Across users? Across back-ends? I'm not sure I see the issue you're referring to so please educate me pls
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:52
Joined
Feb 19, 2002
Messages
42,981
I mean that if multiple users share the same BE, which is typical, one of the FE users should NOT be trying to compact the BE. And since only the BE should ever need compacting, I would need more information to be more specific.

In a properly designed shared application, each user has his own personal copy of the FE and the FE should not bloat because you should not be writing code that causes bloat. The BE grows as records are added and so it needs compacting weekly or monthly depending on how rapidly the database grows. If you are inserting/deleting rows continuously or doing make-tables, there are better methods that don't bloat but we need to know what is causing the bloat and whether it is the FE or BE.
 

cajulien

New member
Local time
Today, 05:52
Joined
Feb 3, 2020
Messages
25
Ha yes, I understand. I had assumed each user has a local FE that links to server BE i.e., no one is sharing FE (I should stipulate this architecture assumption). Agreed that there's generally no reason to C&R FE.

FE users DO NOT use such an admin tool: thus the name "Admin Compact DB tool". I suppose I could setup up a password to make sure end users never stumble onto it and try to use.

It may indeed be worth it to buy such a tool as you mention. I always try to see if some simple tool might do an adequate job first; we'll see how it goes with this client.

Thanks so much for sharing your knowledge
 

Lightwave

Ad astra
Local time
Today, 09:52
Joined
Sep 27, 2004
Messages
1,521
I'm late to the party and plenty of good suggestions above but here is something that I have used in the past it is a combination of a vb script and a ms access function

 

Etxezarreta

Member
Local time
Today, 10:52
Joined
Apr 13, 2020
Messages
175
Hi,
We are several people working on our own FE, and I will migrate the BE into SqlAzure soon.
I guess SqlAzure takes in charge the compacting of the BE, but I am not sure about this.
Thanks i nadvance.
Etxe.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,358
Hi,
We are several people working on our own FE, and I will migrate the BE into SqlAzure soon.
I guess SqlAzure takes in charge the compacting of the BE, but I am not sure about this.
Thanks i nadvance.
Etxe.
Hi. I could be wrong, but I think C&R is only an Access concept because it's a "file system" database. I don't think it applies to SQL Azure. Cheers!
 

Etxezarreta

Member
Local time
Today, 10:52
Joined
Apr 13, 2020
Messages
175
So this means that we don't have to worry about this matter doesnt it?
 

Etxezarreta

Member
Local time
Today, 10:52
Joined
Apr 13, 2020
Messages
175
Ok.
Yes, the FE bloating is one of my main concerns.
Thank you theDBguy.
Etxe.
 

Etxezarreta

Member
Local time
Today, 10:52
Joined
Apr 13, 2020
Messages
175
We don't have a wan or a lan, we will have only local copies of the FE in every computer.
The option compact and repair is activated.
Should me put every day a copy of the FE in a shared file and download it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,358
We don't have a wan or a lan, we will have only local copies of the FE in every computer.
The option compact and repair is activated.
Should me put every day a copy of the FE in a shared file and download it?
If you don't have a LAN, then you can put a master copy of the FE on the local drive and simply copy it each time the user runs the app.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:52
Joined
Feb 19, 2002
Messages
42,981
RDBMS' such as Azure are more efficient about managing data storage than Access but they still require maintenance. That would never be the responsibility of users though. It would always be the responsibility of the DBA (Database Administrator)

If you are not all on a LAN, how are you distributing new copies of the FE.
 

isladogs

MVP / VIP
Local time
Today, 09:52
Joined
Jan 14, 2017
Messages
18,186
Hi. I could be wrong, but I think C&R is only an Access concept because it's a "file system" database. I don't think it applies to SQL Azure. Cheers!
I've no idea regarding SQL Azure as I've never used it, but SQL Server has feature called Shrink which works in a very similar way to compacting in Access
 

Etxezarreta

Member
Local time
Today, 10:52
Joined
Apr 13, 2020
Messages
175
RDBMS' such as Azure are more efficient about managing data storage than Access but they still require maintenance. That would never be the responsibility of users though. It would always be the responsibility of the DBA (Database Administrator)

If you are not all on a LAN, how are you distributing new copies of the FE.
ok, thanks. We will distribute FE copies using sharepoint.
Etxe.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:52
Joined
Feb 28, 2001
Messages
27,001
Almost all active SQL back ends have options to rearrange data segments based on things like "anticipated bucket fill" or whatever else they call it. The idea is that many SQL back ends allow you to "suggest" that each data block or bucket is targeted for only 60% fill rate. So even if you only have 300 MB of data it will occupy 500 MB of space because the buckets don't fill up. That way, if you need to insert a record in a particular block to keep the sort order correct, you have room until you reach whatever threshold triggers a "bucket split" operation, essentially a rewrite of certain segments in the database file to redistribute and re-order data so as to make the DB "optimized" again. ORACLE was pretty big on tools to automate the bucket splits. ShareBase had the same concept. Since the ShareBase engineers left that company to form SYBASE, odds are that the automatic reformatting is part of their package too.

That is a rough description of the operation most closely resembling the equivalent to the Access compact and repair. Of course, every vendor will have a different name for that feature. Heavens forfend that they should all do something that other folks could simply understand without having to first take one of their proprietary DB management classes...
 

Users who are viewing this thread

Top Bottom