May 11, 2010 at 7:58 am
Rather confusing this one.
Running 2008 sp1 on Server 2008 64.
I try to back up my database log and am promptly presented with:
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Seems straightforward right?
Trouble is if I look at the db properties, sys.databases or DATABASEPROPERTYEX(name, 'RECOVERY') I can see it is in FULL recovery.
All I can think of is that the database was in simple before it was enrolled in mirroring, but was subsequently changed to full, backed up and then mirrored.
Perhaps somewhere there is an error in a system table or database header?
Any ideas?
May 11, 2010 at 9:17 am
Is the 'trunc log on checkpoint' option enabled?
Can you try switching to simple, back to full and take a full backup?
You sure that the DB that's failing and the DB that you're checking is the same DB? It's not master that this problem is with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 10:32 am
Its mirrored (sync + synched) - so I don't think we can change it's recovery model.
Yes, we are sure its the right db 🙂
DATABASEPROPERTY(name, 'IsTruncLog') = 0
May 11, 2010 at 10:33 am
I have also tried taking a full (fine) but still unable to take a log.
May 13, 2010 at 1:24 am
Stop the mirroring and take a full back up and try log backup
May 14, 2010 at 6:42 am
In this case I probably could, but not easily if I go to production and experience the same thing.
Also, there are 2TB databases on here, and if I have to do the same for those in production thats us down and out for a day. Not really a goer.
Even if this does 'resolve' the issue, it is far from being a viable fix going forward, so I need to know what the hell is going on.
Cheers for the response though.
Rich
May 14, 2010 at 7:12 am
I would suggest contacting Microsoft's customer support. I have no idea what's happening, I asked a few others if they had any idea, also nothing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2010 at 5:14 am
Thanks GM, might just do that. Will let you know if I find anything.
Rich
May 17, 2010 at 5:39 am
Please do. I'd be very interested in the reason for this odd scenario.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2014 at 10:53 am
Just wanted to bump this one up.
I also just had this happen and I am curious what the solution is, as I haven't found much on the internet about it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply