While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the
SQL Server 2012 SP1. No replication, no mirroring, no log shipping.
Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause.
If the model database has been set to
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.
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
USE [master] GO ALTER DATABASE [DBA] SET RECOVERY SIMPLE WITH NO_WAIT GO
Most likely your database is in FULL recovery. Post the output of following