When model db is set to SIMPLE recovery newly created databases are not truely in SIMPLE recovery - by LondonDBA1

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 765315 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 9/28/2012 5:06:03 PM
Access Restriction Public


Set the model database to SIMPLE recovery. Create a new database and, as expected, its recovery model will show up as SIMPLE. Now backup the database. Then backup the transaction log. This last action should report that log backup is not allowed as the recovery model is simple. However, the log backup succeeds.

The production issue is that we have our model db set to simple, we create fixed size, no-autogrowing transaction log files. We insert data into the database and find that the log space used is constantly growing. It never drops and we encounter log full errors such as this:

'The transaction log for database 'xxx' is full due to 'LOG_BACKUP'.

The DBAs end up scratching their heads trying to figure out why a database in SIMPLE recovery reports log full for a batch of simple insert statements. Worst case is when we don't have a fixed size transaction log and the log keeps autogrowing.

If model starts off as full then you set the user db to simple then you get the expected results that log backups are not allowed.

Also, if model start off as simple and you set the user db to full and then back to simple you get the expected results that log backups are not allowed.
Sign in to post a comment.
Posted by AnupSivaDas (AnupWarrier) on 11/12/2013 at 4:10 PM
Microsoft, Can someone please confirm with which build this is fixed.
Posted by Microsoft on 3/22/2013 at 1:07 PM
We are planning to address this in future cumulative updates for both SQL Server 2012 RTM AND Service Pack 1 versions.

Posted by Microsoft on 2/26/2013 at 11:17 AM
Thank you for your report. We are looking into this issue, and will update when we have more results.
We have been able to reproduce this in-house here.
Posted by jyao on 10/29/2012 at 10:03 PM
I can re-produce this as well. The problem I have is that if I do not do the tlog backup (the db is in simple recovery model), the log file can grow out of control and I need to run the backup log to clean the tlog.

My version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

I hope MS can solve this bug asap.
Posted by AnupSivaDas (AnupWarrier) on 10/1/2012 at 10:54 AM
Able to repro this issue,running RTM bits.This needs to be fixed ASAP.
Posted by Tom Fox on 9/28/2012 at 5:17 PM
Running SQL 2012 CU3, I have been able to reproduce this behavior.