October 31, 2003 at 6:11 pm
I am changing the recovery model on SQL databases from "simple" to "full" via the server (SQL 2000 Ebterprise + sp3). After this is completed I immediately perform a back up and it appears that the model remains at "full". I have nightly backup jobs that run without error, but when I check the recovery model the next day it reverts only some of the databases back to simple. All databases are using the same basic TSQL backup script with different devices. This is happening on a clustered server as well as a regular stand alone. Does anyone have any insight as to why this might be happening?
October 31, 2003 at 9:12 pm
Is it the same databases that always change back to simple?
Run a job to log the recovery model of all databases every few minutes so yo can find out when it happens.
Run the profiler to log interaction to a table so yo can see what happens at that time.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 2, 2003 at 3:45 pm
IS it happening when a maintenance is run the night before? Some of theoptions try to throw the db into single user mode, might mess with the recovery model. Shouldn't, but not sure what's happening.
Any builk inserts occurring?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 2, 2003 at 8:28 pm
The answer to you question is as follows:
The recovery model on msdb reverts to “simple” every time the SQLServerAgent service starts (SQLServer-imposed feature)
November 2, 2003 at 10:18 pm
Is this on all Dbs or only system dbs?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 3, 2003 at 4:25 pm
Happening on 20 databases. Four dbs kept the recovery model at "full". Also, the msdb database is still "full" and we have'nt restarted the server. They all have their own backup device assigned and the backup script is the same, so it's causing confusion.
November 5, 2003 at 6:03 pm
As per Nigels comments re profiling it should throw some light on the problem. If you say you 'manualy' backed them up and they remained at full then when run via jobs some revert to simple, you have to wonder what else the jobs are doing. Are the jobs executing maint plans or executing sql to perform the backups? Does anyone else have access to change DB options perhaps as part of application related data loading 'i.e bulk logged' ? Is it always the same 20 that revert? perhaps choose one of these databases and replicate the jobs that perform the backup to isolate a single case to assist in tracing via profiler.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply