Simple Recovery model

Question

Post reply

Simple Recovery model

vino5786

Right there with Babe

Points: 743
More actions
October 4, 2010 at 10:49 am

#142108

Hi all,
When the recovery model is changed from full recovery to simple recovery will the transaction be logged?
If yes where it will get logged.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms189275.aspx

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

I'm not sure it matters. Once you enter simple mode, you can't recover using the transaction log, and you can't take a log backup. The checkpoint might not write for a minute or so, but it doesn't matter. You can no longer recover to anything beyond your last full + log backups.

The transaction is logged, but since you're in simple mode, the first checkpoint clears it.

Is there a reason you are wondering?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

All data modifications in SQL are always logged in the transaction log, regardless of recovery model. Primarily the difference between full and simple recovery is when the inactive portions of the log are marked as reusable.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 4

Each transaction will be loged into the transaction log for simple, bulk-loged or full recovery.

If simple recovery is set than transaction log truncated after each transaction commits and hence t-log can not be usable and keep the size of the log file smaller. But can't help for point in time recovery.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

vinothr 33504 (10/4/2010)
Hi all,
When the recovery model is changed from full recovery to simple recovery will the transaction be logged?
If yes where it will get logged.

refer these links Transaction Log Physical Architecture

Transaction Log Truncation

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

free_mascot (10/5/2010)
If simple recovery is set than transaction log truncated after each transaction commits

It is not truncated on commit, it is truncated on checkpoint.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

Gail is right. She made it crystal clear. That's the difference.

Thank You,

Best Regards,

SQLBuddy.