Sql-server – Split SSIS project in to multiple files to avoid merge hell

There may be nothing you can do except allow your log file to grow further. Without the exact message we can't be sure, but there are two reasons you might be runing out of log space:

1. You log file is not set to a large enoguh size (or not set to auto-grow), in which case you need adjust these properties.
2. The partition your log file is stored on is full so even if you have log growth enabled it has no-where to grow too. In this case you need more drive space.

Everything your package does will go via the log file: this is an important part of how SQL Server manges transactions in order to keep your data consistent and it can not simply be switched off or temporarily moved. You might find that your package is taking more fully logged action than it needs to in order to acheive the goal. You give no detail of the process your package follows so we can only give general guidance:

• Could the same rows be being modified many times in the process? If so you could try to merge these steps. UPDATE table1 SET fiedl1=1 WHERE ID=1; UPDATE table1 SET fiedl2=2 WHERE ID=1; is two row updates that will be logged where UPDATE table1 SET fiedl1=1, fiedl2=2 WHERE ID=1; is one. Over millions of rows this could stack up quite quickly. Likewise if you insert rows and later update them in the process.
• Are you refreshing a table completely by wiping it down and refilling it? In which case use TRUNCATE TABLE <tablename> instead of DELETE <tablename>. TRUNCATE is a minimally logged operation so will use less log space (and be much faster) then wiping the table by DELETEing every row.
• Better still: if you are deleteing a bunch of rows then replacing them, are many of the replacement rows identical to the ones you initially delete? If so you could update your logic to only UPDATE rows that need updating, INSERT rows that are missing and DELETE rows that are superfluous. For instance one of our apps gets a daily update feed from a client that contains state from another application: this is hundreds of thousands of rows but only a few hundred are added/changed/deleted each day so it is a lot more efficient to detect which few hundred need action instead refreshing everything.

SQL Server uses a cost based formula for determining how to satisfy queries.

"It'll cost 10 random reads in a billion row table vs 1000 sequential reads. I think I can satisfy the needs faster by doing the 1000 reads since they'll all in order" and decision made off it goes. Unfortunately, when the engine opens the data file to find those 1000 sequential rows, the data has been updated and instead of living in there, there's simply a pointer saying "sorry we had to move, try this address in the next state" And you now have 1000 forwarding rows to contend with. The database engine can't go back and say let's try re-evaluating this costing metric because those 10 reads sound a lot better.

That's probably what is happening (simplified) with your data. The statistics on your data distribution are all out of whack after your ELT processing which is typical-you've just moved a pantsload of data so what was true before may not be as true now. Depending on usage, I will have an explicit call to a reindexing job (thank you Ola) after ETL is complete so that now that all my data is up to date, I need to get all the metadata the server uses up to date as well.