asked on

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?

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carla Romere

ASKER

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.

jogos

<<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

Carla Romere

ASKER

As always, thanks for the help.