Automate Compact/Repair

[Lightwave]

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

rounduptheusualsuspects.org - rounduptheusualsuspects Resources and Information.

rounduptheusualsuspects.org is your first and best source for all of the information you’re looking for. From general topics to more of what you would expect to find here, rounduptheusualsuspects.org has it all. We hope you find what you are searching for!

rounduptheusualsuspects.org

 

[Etxezarreta]

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]

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]

So this means that we don't have to worry about this matter doesnt it?

 

[theDBguy]

So this means that we don't have to worry about this matter doesnt it?

No, I don't think so. But, if you're using an Access FE, then you may still have to worry about it for the FE.

 

[Etxezarreta]

Ok.
Yes, the FE bloating is one of my main concerns.
Thank you theDBguy.
Etxe.

 

[theDBguy]

Ok.
Yes, the FE bloating is one of my main concerns.
Thank you theDBguy.
Etxe.

One way to handle that is to simply download a fresh copy of the master FE every time.

 

[Etxezarreta]

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]

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.

 

[isladogs]

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]

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]

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...