Failing SQL Job

I've been passed a sql server scheduled job that's recently started failing.

It basically runs a stored proc that runs a query, the query joins a bunch of tables together and populates a derived table with the results. The query pulls back about 5 years worth of data which weighs in at about 23 million rows.

According to the DBA's the job is filling the transaction log to capacity and then falling over. I'm not really surprised to be honest with that amount of data, but I've been explicitly told not to limit the results being copied into the table.

So my question is, is there any strategy I could use to populate the derived table without making the sql server instance explode in a shower of sparks?

Thanks in advance.

Ideas:

• Put the derived table into a separate database that runs in simple recovery mode (still has to be logged for rollback though)

• Batch the write based on, say data

• Offload processing to SSIS which can batch it for you

StackExchange Database Administrators Q#8653, answer score: 8