0

I'm with a Client that has half of their OpenEdge database filled with unwanted audit records (user built auditing; not OE auditing). The database is around 200GB and 97GB are these audit records. The user built auditing process is now disabled and not used and they simply want rid of the data.

We can't delete the data programmatically because we have no dev tools here. I know the data is safe to delete without causing orphans or referential integrity issues elsewhere in the db.

The audit tables are in their own area as defined in the st file.

They've tested dumping and loading (minus the audit data) but they stopped the load after 4 days because they deemed it too long to be without their primary business database. So this is untenable in a production environment.

They've tried simply removing the data files and editing the st file but they then had problems with backup/restore - probably due to the mismatch on the st file (I wasn't around then to see the problems first hand).

What is the fastest, easiest way to get rid of this data please?

Drammy
  • 940
  • 12
  • 30
  • 1
    You should be able to dump & load 200 GB in about half a day. They are doing something very wrong. – Tom Bascom Aug 10 '16 at 11:43
  • Hmm, interesting - is that dependant on running SSDs? What are the normal pitfalls for a dump & load taking so long - its a pretty straight forward point and click exercise isn't it? Isn't it just hardware dependant? – Drammy Aug 10 '16 at 12:36
  • 1
    That's with pretty crappy HW -- rotating rust and RAID5. If you have *good* HW you should be able to do the whole thing in a couple of hours. – Tom Bascom Aug 10 '16 at 12:49

1 Answers1

3

Proutil "truncate area" should do what you describe.

Once the data is removed via the truncate area then drop the tables & indexes and prostrct remove the offending extents.

(Without dev tools you may have to create a stub storage area and "table move" the EMPTY tables and indexes rather than dropping them.)

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
  • Thanks Tom - I was just looking into this. The Progress documentation says the indices are taken disabled but it doesn't say anything about enabling them afterwards. Are they enabled upon completion of the truncation? They don't want to delete the tables; just the data. Was the table drop simply to recover the disk space from the extents or is it part of the process? – Drammy Aug 10 '16 at 12:36
  • 2
    You would need to re-enable the indexes afterwards. But if the tables are empty that's no big deal. Dropping the tables was just because I thought that was what you wanted to do. If you need to recover the space you should tablemove the tables & indexes to a new (variable size extents) area and then prostrct remove the old area. – Tom Bascom Aug 10 '16 at 12:47