1

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?

1
  • Committing the transaction will NOT free up transaction log space, unless the database is using the simple recovery model.
    – Massimo
    Jun 3 '11 at 14:51
1

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)

1

Perhaps you have already considered (and may already be using) an explicit transaction that commits after X INSERTS have been performed.

These might be useful https://stackoverflow.com/questions/1602244/batch-commit-on-large-insert-operation-in-native-sql

http://sqlserverplanet.com/data-warehouse/transferring-large-amounts-of-data-using-batch-inserts/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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