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:
- Changed recovery to simple.
- Did a shrink files on log file type: now it shows allocated is 1mb and freespace is 60%.
- Ran DBCC OPENTRAN: no open transactions
- Restored the database from backup
- Detached then deleted the log file and attached without a log so it created a new log file.
- 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():
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.
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).