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.

  • It's a sql server 2000 instance. I think it is the temp db filling up not the transaction log yes.
    – PhilH
    Dec 1, 2011 at 15:54

1 Answer 1



  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.