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

I am running into 'transaction log full' issue with SQL Server 2008 R2.

My database includes 80,000 tables which contains up to 5 columns of BIGINT and there is an index for each columns.

The recovery model for the database is set to simple.

The pattern of work with the database is by conducting a large number of bulk insert, update and delete command for about 50 hours while for given time there are only 8 commands are executing in the same period of time.

The transaction log file was set to auto growth by 10 percent and was limited to 2,000 GB (this was defined by mistake since the disk size is only 500 GB).

The 'transaction log full' exception occurred while the database was executing the commands described above and its file size was 41 GB and the log file was 270 GB which use all disk space.

In order to prevent the issue from reoccurring we set the max log file size to 20 GB and removed all transactions in the commands described above (since the use of transaction was not needed).

But unfortunately we experienced this exception again while the log file reached the 20GB limit.

Is it possible that the SQL server doesn’t truncate the log file due to the fact that the server is always executing commands and therefore there is not a single moment were the transaction log is not in use?

What might be the reason for that behavior?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

Transaction log is growing probably because you did these operations in a single transaction instead of break them into smaller transactions. For example, if you delete 1 ML records all toghether, the operation will be first inserted into transaction log and then committed to database. But if you break the delete into smaller transactions, say 10.000 records each, transaction log will be smaller and you will be safe with your available disk space.

share|improve this answer
    
please take a look at our FAQ, specifically the section on Can I use a signature or tagline? as we discourage their use here. –  Ben Pilbrow May 3 '11 at 12:41
    
I broke all my long operations into smaller ones and it did resolve the transaction log issue. The problem is that by breaking the operation into smaller ones the operations cannot be an atomic one. Any ideas on that matter? –  DoronBM May 26 '11 at 4:14
    
Each transaction is an atomic operation by design so if you break a long operation into smaller transactions it will result in many atomic operations. Again if you need to rollback the entire operation (so making it atomic) it has to be entirelly contained into transaction log but this will produce a big tlog. If you only need a result for the entire operation (OK/ERROR) without to be able to rollback all you can use a TRY CATCH statement –  Danilo Brambilla May 27 '11 at 9:13
add comment

Is it possible that you had a single transaction that was running before you started this data run? Transaction logs are written to sequentially and sections of the file can only be re-used when all of the transactions in that section are finished. A single long running transaction (even if it's not doing anything) could stop the log being re-used.

share|improve this answer
add comment

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.