> Alan,
>
> So long as we've agreed that sweeping doesn't have any drawbacks (beyond
> slowdown during the sweep itself). I certainly got the impression from
> our chat you thought nightly sweeping was a bad idea.

I'm just saying it's a non idea - it delivers less than you think in the way
of performance or long db health. It's a backstop for lazy DBAs etc as we
mentioned. In some cases it is indeed a valid and important measure
especially where the nature of your application is specifically in need of
it. Your case does not IMO spell out the need for it at all.

>
> I would absolutely agree that backup/restore is a 'better' solution from
> the point of view of the db file structure - it would just also involve
> making the database unavailable, and introducing the possibility of
> corruption if someone attempts to connect while restore takes place.

You should never just restore to the file which everyone wishes to connect
to, your procedure should be
1. shutdown (or exclude all connections)
2. rename db to another filename (prevents anyone connecting) (maybe divert
page requests to a "site under admin page")
3. backup to another filename
4. restore to yet another filename and if no errors
5. rename restored file to master filename. the renaming procedure is
atomistic.

The frequency of this operation is up to you but I would recommend 6 monthly
in reasonably high traffic sites. You should also do regular backup to
another filename - and restore to yet another filename to test the validity
of your backups. These restores are "throw-away" - they just test the
restore process.

If you follow these steps you will never get corruption.

>
> Hence I don't use it for regular maintenance.
>
> On the point of 20,000 transaction-auto-sweep being "never meant to be a
> frequent event" - it would happen every couple of days for me!
> Disconnected and web-based systems necessarily have a higher transaction
> count than 'connected' apps, I suppose. Ie 1 transaction to populate the
> menu, a second to populate the edit screen, and a third to do the
> UPDATE. Multiply that by 10 users and a couple of hundred views / edits
> per day, and it mounts up!

Be careful - this 20,000 is NOT repeat NOT the number of transactions over a
period of time. It is the difference between the Oldest Interesting
Transaction and a new transaction. You never said before you were dealing
with a web application - in which case the 20,000 limit will NEVER be
reached. I don;t know what method of connecting you utilise but often a web
application will commit by default every after every page, in which case you
are moving the OIT along on every page. The OIT is the first transaction
with a state other than committed. Your rollbacks are likely to be very few
and far between. This default value on my desktop applications hardly ever
gets breached and that environment is far more likely to have very old
interesting transactions.


Alan