log_reuse_wait_desc column says LOG_BACKUP - what should i do ?

Question

log_reuse_wait_desc column says LOG_BACKUP - what should i do ?

S-322532

SSCommitted

Points: 1613
More actions
September 10, 2012 at 10:17 pm

#393150

I'm using Sql Server 2005.
When i tried to compile a stored procedure i got the below message
The transaction log for database 'CDGSYSNCV' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
and when i ran the query below
select log_reuse_wait_desc, * from sys.databases where name ='CDGSYSNCV'
and found the log_reuse_wait_desc as 'LOG_BACKUP'
when i checked here at http://msdn.microsoft.com/en-us/library/ms178534.aspx
i found a note - If the reason is LOG_BACKUP, it may take two backups to actually free the space.
So what should i do. Can anyone help me on this as i don't have control over backup process at my office.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233821 More actions · Answer 1

what is the recovery model used on the database?

When running in full recovery you must ensure you have transactioon log backups in place to truncate the inactive portions of the log.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

S-322532 SSCommitted Points: 1613 More actions · Answer 2

S-322532

SSCommitted

Points: 1613

September 11, 2012 at 1:32 am

#1536026

Hi,

The recovery model is FULL

Perry Whittle SSC Guru Points: 233821 More actions · Answer 3

S-322532 (9/11/2012)
Hi,
The recovery model is FULL

Do you have transaction log backups in force for this database either via scripts or a maintenance plan?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

S-322532 SSCommitted Points: 1613 More actions · Answer 4

Hi Perry,

Yes, we have. Kindly tell me what should i do ?

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

S-322532 (9/11/2012)
Hi Perry,
Yes, we have. Kindly tell me what should i do ?

Ensure that it is running successfully, check the job history to ensure it doesnt fail.

A common problem I have seen is the log backup job fails due to lack of space on the backup drive

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please read through this - Managing Transaction Logs[/url] and this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S-322532 SSCommitted Points: 1613 More actions · Answer 7

S-322532

SSCommitted

Points: 1613

September 11, 2012 at 10:37 pm

#1536554

Thanks GilaMonster 🙂

manisingh2802 Valued Member Points: 55 More actions · Answer 8

Hi,

I am also facing the same issue-

Msg 9002, Level 17, State 2, Line 1

The transaction log for database 'AspenBatch' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Please suggest me how i resolve it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

manisingh2802 (7/3/2015)
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'AspenBatch' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

And what does it say?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree1119 SSC Journeyman Points: 99 More actions · Answer 10

My database recovery mode is simple. Even then it is not shrinking the log files when I tried to shrink it. can some one help me? I'm running the following command.

DBCC SHRINKFILE (XXX_log1, 1)

btw: Log_reuse_wait_desc is 'NOTHING'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Please post new questions in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass