SIMPLE Recovery Model Database waiting on LOG BACKUP!!!!

Recently I’ve encountered a strange issue with one of our databases. This is a SQL Server 2012 Instance(SP1). This is our staging environment and was hosting many databases(Few in SIMPLE and few in FULL Recovery Model).

Note: The Recovery Model for “MODEL” database on this Instance was “SIMPLE”. We kept this way, as we don’t care about Log Backups/Point In time recovery for our Stage Databases.

Issue: Developer complained about error he got with T-Log ran out of space. Well, initially i thought, some one did refreshed this database recently from Production and forgot to chage the database rec model from FULL to SIMPLE. I looked at rec model, It was set to SIMPLE and was never refreshed. Well, now i was almost sure, this is becuase of some active transaction sitting in the log and not letting SQL Server to truncate the log with CHECKPOINTs…

You know what, I was wrong again! When I queried sys.databases for Log_reuse_wait_Desc, I found very interesting and strange behaviour of SQL Server. It was waiting for Log backup to be taken on the database(But, this database is in SIMPLE Recovery Mode) See below for what Am talking about!

1

Cause: This is a BUG in SQL Server 2012 which got fixed in SP1 CU4.( http://support.microsoft.com/kb/2833645)

Bug Number:

1254695 2830400

(http://support.microsoft.com/kb/2830400/ )

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”

Action:  Patch(SP1 CU4) the 2012 SQL Server ASAP. (Btw, CU5 for SP1 is out as well).

Quick Fix: I swapped database recovery model to FULL and took a Full backup and switched it back to SIMPLE recovery model and it was back to normal(Also, I changed the Recovery model for MODEL database to FULL on this Instance till we patched this Server).

Hope this helps if you encounter this strangeeeee Issue!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s