Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SSIS and Transaction Log Growth Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 9:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 6,237, Visits: 7,392
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
Post #1547630
Posted Tuesday, March 4, 2014 10:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Interesting question! thanks Craig.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1547639
Posted Tuesday, March 4, 2014 10:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:00 AM
Points: 1,691, Visits: 757
Nice question, thanks.

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1547641
Posted Wednesday, March 5, 2014 12:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 670, Visits: 902
Interesting Question .......................
Post #1547656
Posted Wednesday, March 5, 2014 1:49 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:20 AM
Points: 4,045, Visits: 206
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.


Post #1547670
Posted Wednesday, March 5, 2014 2:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547682
Posted Wednesday, March 5, 2014 3:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 2,985, Visits: 5,415
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...



Post #1547684
Posted Wednesday, March 5, 2014 3:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 3,914, Visits: 5,093
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”
Post #1547700
Posted Wednesday, March 5, 2014 5:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 5,314, Visits: 9,724
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
Post #1547728
Posted Wednesday, March 5, 2014 5:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547739
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse