need to copy a 600 gig table

Question

need to copy a 600 gig table

Snargables

SSCrazy Eights

Points: 8855
More actions
August 11, 2016 at 10:39 am

#328877

What would be the best way to do this? The db is in simple mode. i need a copy format that doesnt write to trans log.
Ideas?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Snargables (8/11/2016)
What would be the best way to do this? The db is in simple mode. i need a copy format that doesnt write to trans log.
Ideas?

Quick questions, are you copying the table in the same database? How large is the database? What is the copy for?

😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Everything that changes data in a database writes to trans log.

You might have some options to copy the table, but answer Eirikur's questions so we can have more details.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Luis Cazares (8/11/2016)
Everything that changes data in a database writes to trans log.
You might have some options to copy the table, but answer Eirikur's questions so we can have more details.

True enough but, if you hold your mouth just right while you're doing it, you can get the advantage of "Minimal Logging".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Snargables (8/11/2016)
What would be the best way to do this? The db is in simple mode. i need a copy format that doesnt write to trans log.
Ideas?

Where do you want to copy it to? Same database? Different database on same server? Different Server? or ???

Also, now would be a good time to make the realization that your going to choke if you try to eat the elephant in one bite. Now would be a good time to plan on partitioning, even if you "only" have the Standard Edition. If you were to provide some more details on the table (a complete CREATE TABLE statement would simplify that part) and a description of how the table is used (Audit table? History table? Something like an Invoice Detail table? Mostly static temporally based data? Or can any row be updated at any time?).

As Luis already stated, there is no way to write to a database without some logging occurring but you can use "Minimally Logged" techniques to greatly reduce such logging.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 5

Eirikur Eiriksson (8/11/2016)
Snargables (8/11/2016)
What would be the best way to do this? The db is in simple mode. i need a copy format that doesnt write to trans log.
Ideas?
Quick questions, are you copying the table in the same database? How large is the database? What is the copy for?
😎

I guess this is related to Snargables's earlier question - http://www.sqlservercentral.com/Forums/Topic1808915-3739-1.aspx

Thomas Rushton
blog: https://thelonedba.wordpress.com

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Jeff Moden (8/11/2016)
Luis Cazares (8/11/2016)
Everything that changes data in a database writes to trans log.
You might have some options to copy the table, but answer Eirikur's questions so we can have more details.
True enough but, if you hold your mouth just right while you're doing it, you can get the advantage of "Minimal Logging".

That's true, but minimal logging can make the log grow even more than normal logging. With a table that size, in simple recovery mode, I'll prefer to use small transactions instead of a large operation that might never end.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

The OP will have to come back with answers to my questions, otherwise we're only guessing, probably an object restore from a backup would be the way to go here but then again, without the details then Hey Ho!

😎

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

Snargables (8/11/2016)
What would be the best way to do this? The db is in simple mode. i need a copy format that doesnt write to trans log.
Ideas?

Simple Recovery model itself doesn't reduce the amount of transaction logging, just the frequency that SQL Server will truncate log space for committed transactions.

The least transacted, and this fastest, method to move data from TableA to TableB is to do the following, which creates the table and inserts within the same implicit transaction. Since this operation is not modifying an existing object and can be reversed simply by deallocating the extents, SQL Server will use minimal transaction logging. It is by far more faster than inserting into an existing table.

SELECT * INTO TableB FROM TableA;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Snargables SSCrazy Eights Points: 8855 More actions · Answer 9

Sorry guys. This was a drive by. A quick post in the heat of the moment. I thought i was going to have to change the identity on a 2.5 billion record table from int to bigint. I was going to copy the data to a newely created table w the bigint identity. I was going to use ssis so that the trans log wouldnt grow to the same size of the table 600+ gigs. I realized we arent using the identity so i just set it to 1 on insert. Problem solved.

I do however need to build out a partitian strategy. This tables clustered index is on the unique key which is 5 columns (int,date, bigint, bigint, int)

Should i break out the tables on the clustered? I was just going to break them out by month then keep the indexing the same on all of the different partitions then have an indexed view basically unioning them all together.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 10

There is no secret sauce in SSIS that allows you to insert a table without transaction logging.

The following article provides a good explanation of minimal logging and summary of the prerequisites. Basically the thing to keep in mind is that all the work SQL Server does during transaction logging just to faciliate the process of rolling back a table to it's previous state before a DML transaction started, assuming a rollback is needed. If the target table had no previous state, meaning it was created during the transaction, then the rollback scenario and required transaction logging is minimized.

http://www.sqlservercentral.com/articles/Administration/100856/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Snargables SSCrazy Eights Points: 8855 More actions · Answer 11

I didn’t mean not log at all. I just don’t want it to use one continuous transaction to load 2.5 billion records. SSIS is doing it in batches freeing up the trans log for me to shrink it. Regardless, it would have taken entirely too long. Even if I did it in batches in sql it would have taken forever.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Snargables (8/12/2016)
I didn’t mean not log at all. I just don’t want it to use one continuous transaction to load 2.5 billion records. SSIS is doing it in batches freeing up the trans log for me to shrink it. Regardless, it would have taken entirely too long. Even if I did it in batches in sql it would have taken forever.

I'm not sure if you have finished, but don't shrink the log until the whole process finishes. If you shrink it, it will have to grow again and that requires resources. Just keep the transactions at a reasonable size to prevent a major growth.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Snargables (8/12/2016)
Sorry guys. This was a drive by. A quick post in the heat of the moment. I thought i was going to have to change the identity on a 2.5 billion record table from int to bigint. I was going to copy the data to a newely created table w the bigint identity. I was going to use ssis so that the trans log wouldnt grow to the same size of the table 600+ gigs. I realized we arent using the identity so i just set it to 1 on insert. Problem solved.
I do however need to build out a partitian strategy. This tables clustered index is on the unique key which is 5 columns (int,date, bigint, bigint, int)
Should i break out the tables on the clustered? I was just going to break them out by month then keep the indexing the same on all of the different partitions then have an indexed view basically unioning them all together.

Which edition of SQL Server are you using... Standard or Enterprise?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

The engineering DNA of the SQL Server database engine is optimized for online transaction processing, not bulk loading or aggregation.

You may also want to consider BCP-ing the 600 GB out to a native BCP formatted file, and then BCP the data back into the new table. That would bypass a lot of the steps that SQL Server would normally perform internally.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho