SQL Clone
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19567 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
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7207 Visits: 2250
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

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

Hope this helps...

Ford Fairlane
Rock and Roll Detective





PRAMANA.DBA
PRAMANA.DBA
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 1030
Interesting Question .......................
IanSinclair
IanSinclair
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5076 Visits: 345
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
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59475 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rodjkidd
rodjkidd
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6345 Visits: 8673
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
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: 16473 Visits: 7413
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
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33049 Visits: 16637
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
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59475 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
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