22

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.

Deepak N
  • 263
  • 1
  • 3
  • 6
  • 4
    Have you followed the advice in the error message? What did you find there? What research have you done to this point? – Simon Kingston Nov 16 '12 at 17:24
  • Is there actually no database name in the message? – egrunin Nov 16 '12 at 18:04
  • 1
    Try MSDN: [A transaction log grows unexpectedly or becomes full in SQL Server](http://support.microsoft.com/kb/317375) It has a fairly full explanation of the problem. – egrunin Nov 16 '12 at 17:23

5 Answers5

45

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.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
16

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.

Steven
  • 341
  • 2
  • 4
1

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.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
Jihan
  • 29
  • 2
  • 2
    Rather than posting where to find the answer (in the link), you're supposed to distill that information to suit this question. Please see [How do I write a good answer?](/help/how-to-answer) and [How to reference material written by others](/help/referencing) for some guidelines. Link-only answers are considered low quality answers on StackOverflow, and are subject to deletion. – Michael Gaskill Jul 05 '16 at 21:35
0

Do this Right click the database node ->Tasks-> Back Up-> General(in tab)->set "Backup type" to "Transaction Log"-> OK.

Gurgen Sargsyan
  • 1,087
  • 15
  • 22
-1

Right click on the database\Properties\Files.

You will have a table with the database files, among which a log file. Just expand the Initial size to a reasonable amount.

user3243608
  • 1