December 8, 2007 at 9:13 am
I need to copy a large table, in the same database. The table is around 500+ million records, and it is over 50 gigs in space.
I need to copy this table into another table on the same database. I want to limit the growth of the tempdb and also the transactional logs to a very minimal, not more then 5-6 gigs.
When I did this copy in a development environmnet, it grew the tempdb to around 60 gigs and also it grew the transactional logs to around 80 gigs because the new db has index's already built on it.
They way I was doing it was this:
insert into data_new
select * from data
Is there a way of doing this without having the transaction logs grow so large and also tempdb growing so large?
I do not know how to create SSIS packages, I heard they can help me. If someone can tell me step by step what to do, I think I will be able to do that.
I do not have too much experience with SQL 2005 but do have experience with SQL 2000, and I know this was possible using DTS packages in SQL 2000 without have the tempdb and the transactional logs grow so large.
Thanks.
December 8, 2007 at 9:18 am
And also, when I did this is the development machine, the machine was in simple recovery mode.
December 9, 2007 at 4:33 pm
I'm about 98% sure this is correct, someone will correct me if I'm wrong here. 😀
Provided you can do this during a maintenance window, to limit activity, you can ....
1. ***Strongly recommend performing a backup.
2. Change the recovery model to Bulk-Logged
3. Perform the SELECT * INTO Data_New FROM Data
4. Change the recovery model back to Full or Simple (whichever it was in)
5. ***Strongly recommend performing a backup.
The INSERT INTO command will still be logged as normal, so you must use the SELECT INTO command. Also, the Data_New table can not exist prior to this.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 10, 2007 at 6:31 am
Not wrong, at all, but with something this large, I'd suggest the possibility of looking at exporting to a file and then using BULK INSERT to move across. It's a two step process to be sure, but I suspect one that is more easily controlled and certainly takes advantage of the opportunities offered by the BULK INSERT process. Also, having the process in two steps makes it just a bit less painful for recovery. Just a suggestion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2007 at 6:52 am
Another way of doing it is to process a batch of rows at a time in a while loop. For instance copy 100,000 rows at a time until they all got copied. This may take a bit longer but it will minimize locking and temp db usage will be less. You can then also during this process take frequent transaction log backups so it does not grow too much.
December 10, 2007 at 9:49 am
I tested this out using a SSIS package and it worked and did not create as much transaction logs and or tembdb did not grow as big either.
This approach just took longer, it took an hour extra.
The reason I couldnt really do select * into blah_new from blah_old, is I needed to create index's on the blah_new before I could do the import.
The SSIS package, which I built using the Import and Export wizard worked like a charm.
December 10, 2007 at 2:03 pm
BULK INSERT / SELECT INTO would have been twice ( maybe more) as fast and yes recovery model to BULK_LOGGED is mandatory
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply