Recovery Model In sql2008 database

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 2008 Forums

SQL Server Administration (2008)

Recovery Model In sql2008 database

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2010-10-01 : 03:24:35

Dear Member
i have use sql server2008
I have set a recovery model is FULL in my database . but it is changes simple when we restart sql server services.
so what i can do it is not change recovery model.
it is set only full.

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-01 : 12:31:56

Check for SQL Agent jobs that are doing this. If there aren't any, then I'd suggest opening a case with MS as this sounds like a SQL bug.

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

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-01 : 13:42:15

Also check for startup stored procedures. And check the SQL error log as all recovery model changes will be logged.

--
Gail Shaw
SQL Server MVP

SQLPillai
Starting Member

2 Posts

Posted - 2010-10-07 : 11:37:16

I had that happen to me one time, and it was some embedded code in a SQL Agent job that does an ALTER DATABASE that SETs the RECOVERY to SIMPLE. Took me some time to figure it out - as GilaMonster said, you can figure this out by comparing your SQL Server Logs to your schedules.

But if you are seeing this happen only during startups, well, it could be the startup procedures.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-07 : 12:30:09

quote:
Originally posted by SQLPillai

I had that happen to me one time, and it was some embedded code in a SQL Agent job that does an ALTER DATABASE that SETs the RECOVERY to SIMPLE.

That's indicative of someone not understanding the effect this will have on point in time recovery. I'd guess that the recovery model was being switched to simple due to the index rebuilds, however that is a terrible idea. Instead, increase your tlog backup frequency, add disk space to accomodate the large transaction, OR switch to bulk_logged recovery (which I don't recommend anyway as that impacts your point in time recovery options too).

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