Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc?

SQL Server 2012 SP1. No replication, no mirroring, no log shipping.

share|improve this question
1  
This shouldn't be possible. Call product support? –  Jon Seigel May 13 '13 at 17:09
1  
do you or did you at some point have mirroring or log-shipping enabled on this DB? –  Sebastian Meine May 13 '13 at 18:29
    
Nope. Database created just few weeks ago. –  Travis Gan May 13 '13 at 18:36
    
I'm running 2012SP1 as well and haven't seen this issue yet.Could you humor me and just run this to double check select name, database_id, recovery_model_desc, log_reuse_wait_desc from sys.databases. Can you restore the DB to a different instance and test what happens there? –  Ali Razeghi May 13 '13 at 18:58
1  
One of the first thing we did was checking sys.databases. It is confirm that it is in SIMPLE recovery mode, and LOG_BACKUP wait. We did backup the database and restore to another instance, it shows SIMPLE and NOTHING on Log_reuse_wait on other instance. But I don't think restore to another instance is a good way to reproduce the problem as restore operation roll-forward/roll back transaction. –  Travis Gan May 13 '13 at 19:46
add comment

3 Answers 3

up vote 9 down vote accepted

Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause.

MODEL database simple recovery model.

If the model database has been set to SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is on FULL recovery model. Hence waiting for LOG_BACKUP to truncate the log.

I have list out steps on this link to show how I reproduce the problem.

Update, I think the root cause is if a user database was created (not alter after) with simple recovery model, then it has this problem. I blame it on model database as that is the only way to create a user database with the defined recovery model.

More Update, SQL Server 2012 RTM CU7 released last month address this issue. Waiting for SP1 fixes. Wish I found this sooner.

Latest Update, SQL Server 2012 SP1 CU5 released last week (May, 31, 2013) address this issue. Now we are all happy.

share|improve this answer
    
Great investigation, you can mark your answer as the answer. And you got an up-vote for that. –  Marian May 14 '13 at 21:50
2  
I tried. But it doesn't let me to choose my answer for my own question until another 17 hours. –  Travis Gan May 14 '13 at 22:07
    
This is KB 2830400 (support.microsoft.com/kb/2830400) and is fixed in SP1 CU4 or RTM CU7. Just in case CU5 was too much. –  Ben Thul May 1 at 22:25
add comment

I had a similar thing happen - my db was in SIMPLE recovery, and my transaction log was capped too small. I was unable to expand the transaction log with:

USE [master] GO ALTER DATABASE [DBA] MODIFY FILE ( NAME = N'DBA_log', MAXSIZE = 1024000KB ) GO

So, what I did was switch the recovery from SIMPLE to FULL and back to SIMPLE, and that seemed to clear up the issue for me, and I could expand the log, and run a CHECKPOINT. Here was what I ran to resolve it:

USE [master] GO ALTER DATABASE [DBA] SET RECOVERY FULL WITH NO_WAIT GO

... then...

USE [master] GO ALTER DATABASE [DBA] SET RECOVERY SIMPLE WITH NO_WAIT GO

share|improve this answer
    
This doesn't really add much to the information already posted, let alone one year after the fact. –  mustaccio Jun 2 at 17:11
add comment

Most likely your database is in FULL recovery. Post the output of following

select name, log_reuse_wait_desc from sys.databases
where name = '<DB Name Here'
share|improve this answer
    
No It is not. It is on SIMPLE recovery model. –  Travis Gan May 13 '13 at 15:41
    
Thanks for scripting it out. But again, it is on SIMPLE recovery model and LOG_BACKUP on the log_reuse_wait_desc. –  Travis Gan May 13 '13 at 21:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.