SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

Question

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

SQL Server 2012 SP1. Database created just a few weeks ago. No replication, no mirroring, no log shipping, and never has had any of those.

We did backup the database and restore to another instance, it shows SIMPLE and NOTHING in log_reuse_wait on the other instance. But I don't think restore to another instance is a good way to reproduce the problem as restore operation rolls-forward/rolls back transactions.

Answer 1

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

MODEL database set to the SIMPLE recovery model.

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

I have listed the steps in this link to show how I reproduced the problem.

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.

This documented in Microsoft Knowledge Base article 2830400 and is fixed in SQL Server 2012 SP1 CU4 and RTM CU7:

• FIX: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"