need to copy a 600 gig table

Question

need to copy a 600 gig table

Viewing 8 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Luis Cazares (8/12/2016)
but minimal logging can make the log grow even more than normal logging

I've never seen actual minimal logging cause such a thing especially since about the only thing recorded in the log during such operations are page allocations. That's an inherent "reduction" of about 8,191 to 1.

I'm thinking that it's impossible for minimal logging to "make the log grow even more than normal logging" for any given situation but I also know that SQL Server can be fickle at times. If you have the time, I'd love to know more about the circumstances where you've seen this happen so that, maybe, I can duplicate the circumstances with some test data. It would be an incredibly interesting experiment to setup for and prove out.

Thanks, Luis.

--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: 1004363 More actions · Answer 2

Luis Cazares (8/12/2016)
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.

I know you already know this. Just saying it "out loud" for those that might not.

It also requires time... sometimes, a lot of time. Although it helps a huge amount with MDF files, "instant file initialization" only helps a little when it comes to log file growth because each new "segment" has to be "formatted" with VLFs.

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 3

Where can I find info on Log files can use instant file initialization? I thought differently so I am very curious now (unless I read Jeff's post wrong)

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

Jeff Moden (8/16/2016)
Luis Cazares (8/12/2016)
but minimal logging can make the log grow even more than normal logging
I've never seen actual minimal logging cause such a thing especially since about the only thing recorded in the log during such operations are page allocations. That's an inherent "reduction" of about 8,191 to 1.
I'm thinking that it's impossible for minimal logging to "make the log grow even more than normal logging" for any given situation but I also know that SQL Server can be fickle at times. If you have the time, I'd love to know more about the circumstances where you've seen this happen so that, maybe, I can duplicate the circumstances with some test data. It would be an incredibly interesting experiment to setup for and prove out.
Thanks, Luis.

I can't find the reference, it might have been a bug with trace flag 610. It's not that minimal logging used more space for the log, it just allocated more space making the file grow. I no longer remember the exact circumstances, but it might have been fixed by now.

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: 1004363 More actions · Answer 5

BLOB_EATER (8/16/2016)
Where can I find info on Log files can use instant file initialization? I thought differently so I am very curious now (unless I read Jeff's post wrong)

Yes... kind of reading that wrong. Using instant file initialization does virtually nothing for log files because they have to be "formatted" with VLFs.

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 6

Jeff Moden (8/16/2016)
BLOB_EATER (8/16/2016)
Where can I find info on Log files can use instant file initialization? I thought differently so I am very curious now (unless I read Jeff's post wrong)
Yes... kind of reading that wrong. Using instant file initialization does virtually nothing for log files because they have to be "formatted" with VLFs.

Oh I get it now - the parity bits within log sectors.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

BLOB_EATER (8/16/2016)
Jeff Moden (8/16/2016)
BLOB_EATER (8/16/2016)
Where can I find info on Log files can use instant file initialization? I thought differently so I am very curious now (unless I read Jeff's post wrong)
Yes... kind of reading that wrong. Using instant file initialization does virtually nothing for log files because they have to be "formatted" with VLFs.
Oh I get it now - the parity bits within log sectors.

I've not actually studied the structure of a VLF so I can't say whether it's parity bits within log sectors or what. I only know that the VLFs have to be "laid out" in the file (essentially, custom formatting within the file) before the new areas of the file can be used. Someone like Gail Shaw would probably be able to get much more detailed about what goes on during that evolution. I only know that it happens and it takes a comparatively long time and is one of the reasons why I do pay good attention to the size of my production log files... I get that log files are necessary and that they sometimes necessarily get a bit large but the bigger they are, the longer it takes to do a restore, especially in a panic. Because of the improvements we've made to the code in our shop over time, most of our half tera-byte databases require 20GB or less.

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 8

Jeff Moden (8/16/2016)
BLOB_EATER (8/16/2016)
Jeff Moden (8/16/2016)
BLOB_EATER (8/16/2016)
Where can I find info on Log files can use instant file initialization? I thought differently so I am very curious now (unless I read Jeff's post wrong)
Yes... kind of reading that wrong. Using instant file initialization does virtually nothing for log files because they have to be "formatted" with VLFs.
Oh I get it now - the parity bits within log sectors.
I've not actually studied the structure of a VLF so I can't say whether it's parity bits within log sectors or what. I only know that the VLFs have to be "laid out" in the file (essentially, custom formatting within the file) before the new areas of the file can be used. Someone like Gail Shaw would probably be able to get much more detailed about what goes on during that evolution. I only know that it happens and it takes a comparatively long time and is one of the reasons why I do pay good attention to the size of my production log files... I get that log files are necessary and that they sometimes necessarily get a bit large but the bigger they are, the longer it takes to do a restore, especially in a panic. Because of the improvements we've made to the code in our shop over time, most of our half tera-byte databases require 20GB or less.

I went on a "research hunt" about log files and why it cant use IFI and I ended up on Paul Randal's blog post and that's where I got the parity info from - all new to me. 🙂