Solved

Best Way to Archive Gigantic SQL Table

Posted on 2012-04-06
5
246 Views
Last Modified: 2012-06-21
We have an audit table in our ERP database that is 51 gb in size. I need to archive the data in this table and shrink it significantly. I tried selecting all the records to a file, but that took HOURS, filled up the drive space and brought the system to a crawl. I eventually ended up killing that process. But I need to know the best way to archive this data, and how to do that on a regular basis. Once it's down to a manageable size, I could write it to a table on another drive and truncate this one, or at least delete records older than 6 months or something.

I do have a full system backup that runs every night so I have everything backed up, but what I'd really like to do is get this single table onto a blue ray disk or just on another network drive because the odds of someone actually requesting any of this data is truthfully slim to none. But as soon as just truncate the table, someone will want to know something. We tried truncating in our development environment and it was really fast, and irreversible - LOL.

Any suggestions?
0
Comment
Question by:Hers2keep
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
deepakChauhan earned 250 total points
ID: 37816991
In your scenario first create  a secondary file group on another shared drive or other secondary drive  and partition this table on this newly created file group on the basis of data older than six months.

to do this first created a filegroup with at least one file and created partition functions and partition schema then move this table to on the file group as according partition functions.
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 37817554
For future:
Can you move that audit table to a own database? Then when it becomes big you just backup-up it and save it for the archives. You truncate table and start again for a new period.
Has also advantages
- for security reasons, less people who have permission on the whole database.
- large table does not put stress on maintenance (backup/restore/index/statistics) of your production-db  

Now:
You want to keep that data usable, best way is in a db-version. Make a new db simple recover model. Copy in manageble parts (+ commit  for log-file!) the records to new db.  Backup this db for your archive and drop it.
You can do this in db-units for example for audit logs of 6 months / database.

After the whole actual data is copied then you can indeed truncate the table on production.
0
 

Author Comment

by:Hers2keep
ID: 37817572
Unfortunately, I can't move the audit table to another database. It's built in to our ERP system. One of the problems was we were auditing too many fields on one of the tables we were auditing and it was creating hundreds of lines for one change. We've modified the tables being audited so it won't be growing out of control again, I just have to get this data itself backed up somewhere I can get to it and then I can truncate and start fresh. I will be digging in to this on Tuesday of next week to see if I can get somewhere.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37817616
<<One of the problems was we were auditing too many fields >>
The classic  ... but have seen worse auditing all on initial load of a huge ERP. Why is it so slow and why does it takes so much space? Yeh right
0
 

Author Closing Comment

by:Hers2keep
ID: 37833981
As always, thanks for the help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now