Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and Transaction Log Growth


SSIS and Transaction Log Growth

Author
Message
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5703 Visits: 7660
Comments posted to this topic are about the item SSIS and Transaction Log Growth


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2881 Visits: 2235
Interesting question! thanks Craig.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Ford Fairlane
Ford Fairlane
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 Visits: 836
Nice question, thanks.

Hope this helps...

Ford Fairlane
Rock and Roll Detective





PRAMANA.DBA
PRAMANA.DBA
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 1029
Interesting Question .......................
IanSinclair
IanSinclair
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4910 Visits: 329
And if the Maximum Insert Commit Size in the OLEDB destination is already set to a reasonable value you.....
This option might not work - so plitting a source stream into a few hundred streams might be unwieldy but necessary.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
Interesting question, but I do not agree entirely with the answer.
Setting the commit size to a lower value will commit batches before the entire load is done. What if something fails and I want to roll everything back?

Splitting the load up in smaller chunks but in manageable transactions seems to make more sense.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rodjkidd
rodjkidd
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3659 Visits: 8037
Koen,

I agree with what you say, nothing worse than a partially loaded set of data in this type of scenario, but the question did say the simplest, and changing the commit batch is very simple.
So it's the simplest but maybe not the most sensible for a given situation.

Rodders...



Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5800 Visits: 7137
Alternatively, change the recovery model to bulk logged just before the load and back on completion thereof.
however, this will require a full backup before and after. a log backup before and after is recommended.
Bulk logged reduces log space usage by using minimal logging for most bulk operations.

see Prerequisites for Minimal Logging in Bulk Import for more information in this regard

good question, thanks Craig

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7474 Visits: 15142
Koen Verbeeck (3/5/2014)
Interesting question, but I do not agree entirely with the answer.
Setting the commit size to a lower value will commit batches before the entire load is done. What if something fails and I want to roll everything back?

Splitting the load up in smaller chunks but in manageable transactions seems to make more sense.

Koen, I don't understand. Setting the commit size to a lower value is having SSIS split the load into more manageable chunks for you, isn't it? If there's a failure, you're not going to be able to roll the whole thing back in either situation (unless, of course, the failure occurs in the first batch).

Stewart "Arturius" Campbell (3/5/2014)
Alternatively, change the recovery model to bulk logged just before the load and back on completion thereof.
however, this will require a full backup before and after...

Stewart, I think you only need the full backup if you switch to Simple. Bulk Logged preserves the log backup chain, although if you need to restore to a point in time during the time the database is in Bulk Logged mode, you may not be able to.

John
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
John Mitchell-245523 (3/5/2014)
Koen Verbeeck (3/5/2014)
Interesting question, but I do not agree entirely with the answer.
Setting the commit size to a lower value will commit batches before the entire load is done. What if something fails and I want to roll everything back?

Splitting the load up in smaller chunks but in manageable transactions seems to make more sense.

Koen, I don't understand. Setting the commit size to a lower value is having SSIS split the load into more manageable chunks for you, isn't it? If there's a failure, you're not going to be able to roll the whole thing back in either situation (unless, of course, the failure occurs in the first batch).


You're right. You have more control over the different transactions (which are smaller in size), but to avoid transaction log growth you still have to commit them.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search