Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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)

share|improve this answer

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 http://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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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