May 7, 2009 at 9:01 am
Hi Guys,
Iam New to SQL Server Database Administration. For implementing Backup&Restore We have choosen Full Recovery Model. but all of sudden the recovery model was switched from Full Recovery model to Simple recovery model. can any body help me how to trouble shoot this.
Thanks
Pradeep Kumar
May 7, 2009 at 9:08 am
Can you explain the complete sceanrio? What exactly are u doing?:-)
May 7, 2009 at 9:11 am
The recovery model doesn't change unless a script that specifically changes it.
I remember hearing about some performance/monitoring tool doing that in the past if the monitoring was being logged to that particular database..
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 7, 2009 at 9:14 am
Iam taking back up of my database. on daily basis. Every sunday we used to take full back up nd every day night 12:00 we used to take Differential backup and every day for every one hour we used to take Transactional log backup . for this we have choosen Full Recovery Model. but all of sudden the recovery model was swithced to Simple recovery model.
May 7, 2009 at 9:23 am
Taking backups wont change the model of the database untill script mentions to do so.
Please check your script.
May 7, 2009 at 9:47 am
Either there's a job that has a command in it to change the recovery model, or someone ran a command to do it directly. SQL won't automatically change recovery models.
There will be an entry in the error log stating that the recovery model has changed, it won't give the username, it will give time, database and SPID.
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 7, 2009 at 10:01 am
There must be some process that is changing the mode. You might need to run a trace to try and catch this, or run a script that checks it every 10 minutes, which would narrow down when it gets changed.
The mode doesn't change by itself. You could possibly have some maintenance task that changes it for some reason, or some load process. But something is doing this, not SQL Server
May 7, 2009 at 1:42 pm
How are you being notified that the recovery model has changed? Do your backups start failing 'all of a sudden'?
If the following command is executed, then you will not be able to perform transaction log backups until a full backup has been run: BACKUP LOG ... WITH TRUNCATE_ONLY.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 10, 2009 at 12:33 pm
nadellapradeep (5/7/2009)
Hi Guys,Iam New to SQL Server Database Administration. For implementing Backup&Restore We have choosen Full Recovery Model. but all of sudden the recovery model was switched from Full Recovery model to Simple recovery model. can any body help me how to trouble shoot this.
Thanks
Pradeep Kumar
Check out for any jobs that are runing against your server? Investigate into them. In SQL error log check for the entry when the databases is changing its recovery model and look for jobs that are running at the same time and troubleshoot it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply