title: How to reduce transaction log size for an SSIS ETL process?
body: <p>We have an issue with a transaction log growing to a size of about 70 GB for a SQL Server 2008 database of about 10 GB. This happens in the staging database of an ETL process run with SSIS 2008. The process takes about 8 hours to complete and is run on a weekly basis.</p>
  
  <p>70 GB is getting close to the physical limits of the system and the I-O alone will have a performance penalty.</p>
  
  <p>We will never make use of the logs for recovery of the staging database, since the ETL will start all over again with each run and restore an earlier database backup on process failures. At this point, an incremental ETL process would be hard to implement, because the configuration can change between runs, changing many or all of the calculations.</p>
  
  <p>The database recovery model is set to simple. SISS can be allowed exclusive access to the staging database.</p>
  
  <p>We wonder what can be expected from tweaking SISS.</p>
  
  <p>Some considerations:</p>
  
  <ul>
  <li>Multiple connections seem to made by SISS to the database. How could this interfere with tweaking the transaction isolation levels of packages and the 'length' of transactions, i.e. commit after certain steps are completed?</li>
  <li>Could the process performance increase when running in single user mode? Could SISS cope with that?</li>
  <li>Would it be wise to just configure packages to run with setting Transaction = Unsupported?</li>
  </ul>
  
  <p>Any experiences, thoughts or recommendations on this issue?</p>