Select Page

Data Purging for Effective Database Management

John Kaufling | | September 8, 2015

Data Purging for Effective Database ManagementAs data gathering becomes an around-the-clock necessity for competitive organizations, the question of data retention arises.

How long is too long to keep bits and bytes of potentially obsolete information around? When the period for legally retaining data has passed, should you purge your data?

The answer is an emphatic, “yes!” To ensure your databases continue functioning effectively and successfully, the periodic purging of infrequently accessed data is a must. Without it, database reporting takes longer and systems dependent on that data, such as business intelligence systems, also crawl to a halt.

Purging Policies

Once data is no longer needed for specific legal or business requirements, it should be purged. As my colleague Chuck Ezell explained to Abe Selig of Plotting Success, “It’s one of the first things I look for. … What’s your data retention policy, and do you need 10 years’ worth of data in your tables?”

Data retention policies vary by organization based on the type of business they are conducting. Ezell explains:

Publicly traded companies are definitely going to have a five-, 10-, maybe even a 15-year retention policy, particularly for data such as accounting records that they may need to show SOX [Sarbanes-Oxley Act] compliance.

When data is not purged regularly, the tables in a database can grow so large that performance begins to deteriorate.

What Data Is Necessary?

There are several strategies that can be employed to remove data. Ezell says the first data to be purged is any created without human interaction, such as Web server logs or call detail records.

Most organizations need to keep data accessible for roughly 24 months. After that period, some older data may need to be periodically archived. Any nonessential data can be a candidate for purging.

Others advocate creating a procedure or framework for data purging. This might include using a tool or script to effectively automate the process. Some database management systems include command-driven utilities that are simple to use.

The data purging process also necessitates establishing some sort of definitions or schedules for various types of data. This applies not only to data currently being used, but also dormant information, such as older transactions.

Although data purging may be a simple process, changes can affect parts of the remaining system, such as tables. Retaining pertinent data is important to ensure these continue to function properly. This is particularly true for relational database systems that may rely on some pieces of data across tables.

Benefits Beyond Speed

Purging, if properly completed on a routine basis, can accelerate database reporting. Ezell notes:

A recent client of mine was experiencing reporting slowdowns and needed to purge data and reorganize his tables. … After we did that — and it took 36 hours of work, as his tables were huge and took large amounts of time to reorganize — he benefited from a 20-30 percent speed increase, simply from purging old data out of his system.

What’s the ultimate benefit beyond performance? Sanjay Joshi, writing on SQL Server Pro explains, “companies can … more precisely estimate future storage and server needs. Thus, ROI will improve for existing hardware, maintenance, and future hardware requisitions.”

Does your organization need assistance in assessing the performance gains and cost savings associated with an effective data purging routine? Datavail can help. Contact us for more information.

Image by exopixel/123RF.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.