INTRODUCTION
This document records steps, scripts, and ideology behind the maintenance performed on a production database between 22nd April 2015 and 23z April 2015. The details are specific to an application, but the principles may be useful to others in need of doing similar operations in production.
KGROUND
The database had grown to over 1.44TB with the table EPOEvents consuming over half of this size (792GB). Efforts had been made in the past to delete entries in this table older than 90 days. Unfortunately, it proved futile. The job scheduled to achieve this could never complete during any session. The reason was the poor performance of the database.
Upon discussion, a decision was made to purge the entire EPOEvents table by truncating it. The further decision was to prepare the database correctly for the expected data growth in the future.
The details of the database server in question are below:
EPOEVENTS TABLE
The script defining the EPOEvents table is documented in Appendix I.
It is worth mentioning that the AutoID column of this table is in Foreign Key relationships with the tables HIP8_EventInfo, HIP8_IPSEventParameter, and SCOR_EVENTS. This affected the approach used to truncate the EPOEvents table. The summary details are in the table below:
This information was extracted using the statement in Listing 1.
