Transaction log reported full but is not

Batch jobs error emails report:


The transaction log for database 'WebDB' is full.

No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of space. SQL Server 2008 SP3, but not R2.

I'm a developer, we no longer have a DBA. So it falls to me to try and fix this.

In all probability you had a large transaction that filled up your transaction log. When the transaction failed it rolled back. This cleared up your transaction log.

If that particular transaction needs to be processed then you can either

• Modify your log size and max log size - This is best if this is a regular process. Running a transaction log backup right afterwards is not a bad idea either.

• Change the code to reduce the amount of transaction space needed. This could be by using a minimally logged operation or by doing several smaller transactions (with a transaction backup in the middle).

StackExchange Database Administrators Q#87666, answer score: 5