Best Way to Archive Gigantic SQL Table

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?
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
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
Carla RomereDirector of Information TechnologyAuthor Commented:
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
jogosCommented:
<<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
Carla RomereDirector of Information TechnologyAuthor Commented:
As always, thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.