The transaction log for database 'test' is full due to 'LOG_BACKUP'. and free space issue with C Drive

Question

I am using MSSQL for storing data.When I tried to delete the data in a table, it shows an error

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

Also, I have checked the size of C drive, and it almost full now, before doing this delete operation, the free space was 10GB.

When I searched for the error, I found that backing up the transaction log will resolve the issue.I did the same and still, C drive remains full.

What I can do to resolve the issue?

Answer 1

As per the comments, I have shrunk the transaction log file, it freed up around 13GB. Since the recovery model of my database is Full, first I have taken a backup of transaction logs and then change the recovery model of my database to Simple. After that, I have shrunk my log files and again set back to Full.

 ALTER DATABASE AdventureWorks2012
    SET RECOVERY SIMPLE
    GO
    DBCC SHRINKFILE (AdventureWorks2012_log, 1)
    GO
    ALTER DATABASE AdventureWorks2012
    SET RECOVERY FULL

https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/

Thanks, @sepupic, for the very accurate approach you have mentioned in the post. After cleaned up space, I have deleted the records in small batches as per the comments of @Pedro Martins.

Answer 2

Your database is in FULL recovery model. Your log is full because you don't take regular log backups, and this is the only thing that clears the log in full recovery model. So what you need now is log backup. This will truncate your log. Log truncation does not change log size but it permits the log to be reused.

When I searched for the error, I found that backing up the transaction log will resolve the issue.I did the same and still, C drive remains full.

As I said, log truncation just permits to log to be overwritten, it does not change its size.

What is your actual ldf and mdf file sizes? If your log is bigger than your data file it means that your backup strategy is wrong and you need to shrink log file to appropriate size.

You also need to plan regular log backups or valuate whether simple recovery model is better for you.