Greetings All,

Firebird 1.5.3
Delphi 5.1

My goal is to do house keeping on all of the history tables in our database and keep the last 30 days on some tables and the last 30 activities on others. Then do a backup and restore to make the size of the database smaller.

Please keep in mind that I did the following on a backup of the production database so that I could test my work and not affect the production database until I'm sure this will work.

One of the tables in our database (DIALER_RESULTS) stores results from a predictive dialer. The table currently has over 5-1/2 million records in it and we have decided to do some house keeping on this table by keeping the last 30 activity results for each account and deleting the rest of the records.

I have created a program that selects all records from the DIALER_RESULTS_ACCT table into a TClientDataSet (This table has just one field ACCT_ID) and then loop through each record in this dataset which then calls a stored procedure that selects all records for this account in descending order and deletes any record past the first 30 records.

I ran this program on a weekend with no one else around (as I ran it on the same server used for the production database) and it successfully did the job in several hours returning that over 3-1/2 million records were deleted.

Q: Should I have done a select statement against this database to force garbage collection?

Q: Is it a good thing to delete this many records at one time from one table?

Q: Any gotchas when it comes to doing a backup and restore? The backup normally takes a few hours to run on a 6-1/2 GB database. Will the backup take longer now because of the mass delete?

Q: Any guidelines available on the best ways to do a mass delete and what all should be done to make the size of the database smaller?

I would appreciate any insight into this.

Thanks,
Mike

[Non-text portions of this message have been removed]