Written by 04:37 Database administation, Memory Optimization

Workplace Encounters: Reclaiming Space from an Oversized Database

Workplace Encounters: Reclaiming Space from an Oversized Database

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.

(Visited 18 times, 1 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close