THe Transaction log for database is full

Question

Hello,
I been facing below error from last two days.
I took full backup and Transaction log backup after that but still some SQL jobs has been failing with below error.

Error :- The transaction log for database 'DB' is full due to 'LOG_BACKUP'. [SQLSTATE 42000] (Error 9002). The step failed.

Drive is empty where we save our logs files at.
What should causing this?

Answer 1

I can't say how large your log files has to be, but they are now extremely small compared to the database size. Perhaps start by having just one log file and make it some 20% of the data size, as a starting point. The configure a reasonable autogrow fot hat log file. Not pecentage. Perhaps 1 GB, as a staring point. And don't shrink the log file (in case you do).

Answer 2

What returns this query for the database?

select name, log_reuse_wait_desc
from sys.databases 

Answer 3

Perhaps the log was full while that job was running, hence the job failed. But at a later time you emptied the log by doing a log backup.

I.e., you need a larger ldf file, quite simply!

Answer 4

Hi @Jinal Contractor ,

>Error :- The transaction log for database 'DB' is full due to 'LOG_BACKUP'. [SQLSTATE 42000] (Error 9002). The step failed.

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error.

This document records some general recommendations when you are working with transaction log files.
Manage the size of the transaction log file