Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2005-04-19 : 21:24:04
|
| Hi all, I have come across this problem several times: Suppoe I changed the recovery model of several databases (including the msdb system database and several user databases) from "Simple" to "Full" using Enterprise Manager. Somehow, when the server is rebooted (or the service is restarted, not sure which one causes this problem), the recovery model is reverted back to "Simple", causing transaction log backup jobs to fail. Is there anything I can set to prevent this from changing automatically?Thanks,delpiero |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-19 : 21:54:46
|
| The only way this could change is if you or someone else created a startup procedure or job that runs sp_dboption to change the recovery model. Check your job list for any jobs that run on startup, and run this query in all of your databases:select * from sysobjects where objectproperty(id, 'ExecIsStartup')=1If you get any results, those procedures are marked as startup procedures. If you don't find anything that changes the recovery model, then someone is changing it manually. |
 |
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-04-28 : 23:47:01
|
| Sorry to come back to this problem so late. I have run the query and found NO RESULTS in all databases. The databases which encounter this problem include msdb, model and one user database. I found that the msdb ALWAYS encounters this problem and the service is restarted, while the other two occur occasionally. Very strange. I guess no one can be changing them manually. It is just my development workstation...delpiero |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-04-29 : 01:42:33
|
| you should never change the recovery model of any system databases. The 4 common system databases include master, model, msdb and tempdb. This is probably a feature and not a bug.-ec |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-05-03 : 04:36:18
|
| Well, not only the recovery model of the system databases change after restart, but the user databases' recovery model also change. One thing to note is that I am using SQL Server 2000 Personal Edition on my desktop. The default recovery model when creating a new database on Personal Edition is "Simple". However, I am not sure there is a default option which reverts the database back to simple recovery model after restart, when it is a full recovery model before restart. Thanks,delpiero |
|
|
|
|
|
|
|
|
Recovery model automatically changed