The transaction log for database 'databasename' is full.

Question

The transaction log for database '' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. Please Help me to solve this issue. Thanks in advance.

Answer 1

Undoubtedly, you are running the database in Full Recovery Mode, and failed to take Transaction log backups.

Right-click your database in SQL Server Manager, and check the Options page. Switch Recovery Model from Full to Simple then right-click the database again. Select Tasks Shrink, Files. Shrink the log file to a proper size (I generally stick to 20-25% of the size of the data files).

Switch back to Full Recovery Model, and take a full database backup straight away.

You now need to figure out whether you need the ability to recover your database to a point-in-time. If so, schedule regular transaction log and perhaps differential backups according to the requirements of your business.

In case the business can live with a certain amount of data loss, schedule full backups and switch to Simple mode.

Answer 2

The most likely answer is you need to run log backups or there is an open transaction.

Here's a little more to help you out...

Run this script on your server:

-- last FULL backup
;with FULLBUs 
as (
    select d.name, max(b.backup_finish_date) as 'Last FULL Backup'
    from sys.databases d
        join msdb.dbo.backupset b
            on d.name = b.database_name
    where b.type = 'D'
    group by d.name
),

-- last LOG backup for FULL and BULK_LOGGED databases
LOGBUs
as (
    select d.name, max(b.backup_finish_date) as 'Last LOG Backup'
    from sys.databases d
        join msdb.dbo.backupset b
            on d.name = b.database_name
    where d.recovery_model_desc <> 'SIMPLE'
        and b.type = 'L'
    group by d.name
)

-- general overview of databases, recovery model, and what is filling the log, last FULL, last LOG
select d.name, d.state_desc, d.recovery_model_desc, d.log_reuse_wait_desc, f.[Last FULL Backup], l.[Last LOG Backup]
from sys.databases d
    left outer join FULLBUs f
        on d.name = f.name
    left outer join LOGBUs l
        on d.name = l.name
where d.name not in ('model', 'TempDB')
order by d.name

This query will give you a rough overview of your databases, what recovery model they are using, why the log is full, and when your last FULL and LOG backups were run.

Look at the column marked log_reuse_wait_description. Most likely it says BACKUP. Next most likely cause is TRANSACTION.

If it is BACKUP here is some info:

Basically, for your SIMPLE databases, run a FULL backup every day. For your FULL databases, run a FULL backup every day, and a LOG backup every hour. Adjust the frequency of your LOG databases to match your ability to lose data while keeping your job.

The simplest way to manage your backups is to use Ola Hallengren's maintenance scripts. Visit his website and try using them.

If you see TRANSACTION as the reason, try running:

dbcc opentran

And track down whoever is has the open transactions.

Answer 3

If your database recovery model is full and you didn't have a log backup maintenance plan, you will get this error because the transaction log becomes full due to LOG_BACKUP.

this is will prevent you to perform any action on this database like a shrink , and the SQL Server Database Engine will raise a 9002 error.

to overcome this behavior I advise you to check this The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP that show detail steps to solve this issue like log backup maintenance plan.