I have a long running INSERT statement in MS SQL Server. After about an hour, the transaction log runs out of space. Is it possible to set the auto-commit interval of a session so that it flushes the transaction log after so many records have been inserted?
The answer by jl is good. I would also consider looking at your insert strategy. Could you perhaps leverage the efficiencies gained by Bulk Inserts? If Bulk Inserts aren't your answer, then cutting up your job into batches of inserts as jl suggested is probably the way to go.
One other thing to consider is dropping your indexes prior to inserts and recreating them after your insert. For a job this intensive, that could significantly cut down the time to execute it. (See here)
Perhaps you have already considered (and may already be using) an explicit transaction that commits after X INSERTS have been performed.