Bob,

> I've got a 10GB database that contains four years of information. We
> really only need to have two years available in this database for online
> use. While we should, theoretically, be just fine keeping all four years
> in the same DB, we have found the file size to be difficult to
> handle. What I would like to do is split this DB into two, making one a
> read only archive of years 1 and 2, and keeping years 3 and 4 online.
>
> I'm looking for the best approach to accomplishing this. I have started

by

> making a copy of the database, from which I will delete years 3 and 4. I
> also plan on deleting years 1 and 2 from the 'online' copy. My plan is to
> make the deletions, immediately do a backup without garbage collection,
> then do a restore.

For one of my main applications, I use a fiscal archive. IOW, every year,
the adminstrator clicks a button and the program creates a fiscal archive
copy of the database with records for the past year. They are then cleared
from the main database. I don't do a backup and restore but it could be done
easy enough. I find that I don't need it, generally. The archive database is
named with the fiscal year dates so you can tell them apart. It also stores
this name in a table of the main DB so that the program can always access
the old records for searching, etc. simply by searching through the
databases listed in the table. I have found this system to be very reliable
and easy to maintain.

>
> Is there any reason why I should delete in increments, committing after
> each? Or is it safe to delete a million records in one transaction?

It is usually safe, however, depending on the number of indices that need to
be updated, any triggers firing, etc. the time involved may vary
significantly. In general, there is no reason why it can't be done this way,
though.

>
> Any other tips appreciated,

When changing, moving this much information, IMO it is best to do it with no
one else accessing the DB. Preferably during a weekend or nights when little
or no activity is being performed.

HTH

Woody (TMW)