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.