1

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?

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
3
  • 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, 2011 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, 2011 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, 2011 at 9:13
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .