SQL Backup Question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Backup Question

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-03-31 : 16:36:46

I have changed all of my databases to simple recovery model after reducing my log files.
Came into work this morning, and noticed that all databases have been changed back to full recovery model.
Anyone have an idea as to what could cause this? If our databases are being backed up by a 3rd party tool, could they have gotten reverted by this tool? Or am I missing something that needs to be done in SQL Server in order to keep the simple setting in place?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 16:40:56

Sure a third party tool could have done it. You'll need to run a trace to capture what is causing it.

Nothing needs to be done to keep it in simple mode, something/someone is doing it on your system.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-03-31 : 16:46:25

Great, thanks for the info!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-31 : 17:07:08

The change to the recovery model is captured in the default trace; I believe the EventClass is 164.

declare @trace_file nvarchar(500)
select  @trace_file = path from sys.traces where id = 1 

select
	*  
from
	[fn_trace_gettable](@trace_file, default) 
where
	eventclass = 164 and 
	starttime >= dateadd(dd,-2,getdate())
order by
	starttime desc

CODO ERGO SUM

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-04-01 : 07:47:07

When a DB is in Simple recovery mode, will it still write to the logs on a minimum basis, perhaps with system info? I was under the impression that the log will cease to grow, ours still grow minutely.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-04-01 : 07:49:41

That's a useful query!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-01 : 12:36:24

The tlog is used the same for SIMPLE as it is for FULL. The log will NOT cease to grow with SIMPLE.

The difference between SIMPLE/FULL is what happens AFTER a transaction completes. You need to do some reading on this topic as it is widely discussed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources