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

09-10-2012

I got this error today for one of my test databases.

Following are the steps to solve this.

Step 1

The error message gives a hint to look in the log_reuse_wait_desc column in sys.databases table. So we will just do it.

select name,log_reuse_wait_desc from sys.databases;

This gave me following result for my database.

name               log_reuse_wait_desc
RP2_TEST    LOG_BACKUP

If what you get against your database is a different thing then don’t worry next step will still help you.

Step 2

This MSDN page describes the reason behind each possible value you can get for the above query as well as what you should do to get around this.

For me the instruction was to simply create a Transaction Log Backup

I created a Transaction log backup and walla, everything is normal again.

This is what I got for the same query after the transaction log:

select name,log_reuse_wait_desc from sys.databases;

This gave me following result for my database.

name               log_reuse_wait_desc
RP2_TEST    LOG_BACKUP

I hope this helped. Please comment if you got a different problem and/or a different solution so we can help someone else together!

Menol

ILT