The transaction log for database 'database' is full due to 'LOG_BACKUP'. Nothing works

Question

I have about 30 databases on SQL server 2012. One of the databases is erroring with "The transaction log for database 'database' is full due to 'LOG_BACKUP'."

I have spent several hours googling and implementing the suggestions.

Here are the things I have tried:

1. Changed recovery to simple.
2. Did a shrink files on log file type: now it shows allocated is 1mb and freespace is 60%.
3. Ran DBCC OPENTRAN: no open transactions
4. Restored the database from backup
5. Detached then deleted the log file and attached without a log so it created a new log file.
6. Check disk space: 35 gigs free.

None of that worked I still get the same error. Any ideas? I am desperate!!!!

Log file growth is set to unlimited and auto growth is enabled. There are no relevant file system errors or SQL Server errors in the Event Viewer. I only have one drive on this server so I cannot move to another disk.

Results of DBCC LOGINFO():

@@VERSION:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

The error occurs constantly, always the same error even after reboot.

Overnight the database came back online and all is working again! I did nothing to it, it just started working. Needless to say I am baffled and weary. I would like to keep this topic open for a while in case it happens again.

UPDATE:

The issue reocurred on the 19th, this is close to one week after it originally occurred. This time I noticed an error in the system events around the time it went offline:

sqlservr (1376) An attempt to open the file "C:\Windows\system32\LogFiles\Sum\Api.log" for read only access failed with system error 5 (0x00000005): "Access is denied. ". The open file operation will fail with error -1032 (0xfffffbf8).

Answer 1

Performing full database backups does not truncate the transaction log. In order to truncate the transaction log you have to perform a transaction log backup. If you changed the recovery model to simple and still received the error, then this means you had active transactions that could not be cleared by checkpoint.

Relying on autogrow for capacity planning is not a good idea. A better idea is to set your transaction log large enough so that auto grow does not occur. I don't see where you tried to manually increase your transaction log size in your troubleshooting steps, nor do I see where you tried to perform a transaction log backup.

Another thing you could have tried is to add another transaction log file, just to see if that would resolve the error and allow processing to continue. I would remove that additional file after completing my troubleshooting.

There are a lot of possibilities here, so I would recommend setting the log size larger than it was before, change the autogrow settings larger than 1mb and set up frequent, regular transaction log backups to keep the tranlog size on the smaller side.

If you don't want to mess with tranlog backups, then keep the recovery model on simple, but realize this means you will only be able to restore back to your last full backup, and it is still possible for a tranlog to fill up when in simple recovery mode.