February 22, 2007 at 12:19 am
I have schedueled a backup routine on our server that makes a full backup at midnight, and 2 transaction logs at lunchtime and afternoon. All the databases are set to FULL recovery model, but today I got a mail from my job, stating a failure. So I checked what went wrong, and the error was that it could not make transaction log of msdb, because it was set to SIMPLE.
Why is it automatically set to simple? I read that when the sql agent restarts, it's set to simple, but how can i avoid this?
February 22, 2007 at 3:07 am
Well, it doesn't look to reset the model when the agent starts. I changed the model and restarted the agent and it didn't reset the model for msdb. So looks like it is working fine for me. But it does reset the model in the following case. This is from BOL...
'we recommend that you use the full recovery model for msdb and that you consider placing the msdb transaction log on fault tolerant storage.'
When SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to SIMPLE.
May be some of above happened on your database.
February 22, 2007 at 3:25 am
please don't multi-post.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply