Why do databases in Simple Recovery Mode need transaction log?

Question

Why do databases in Simple Recovery Mode need transaction log?

hl6a

Ten Centuries

Points: 1004
More actions
September 6, 2013 at 7:07 am

#297562

I am trying to understand more about how SQL Server works. And I spent some time reading articles about the checkpoint process and the lazy writer. Then I came to this question - it may be a stupid question to many of you...
In Simple recovery mode, the transaction log is truncated with a checkpoint. The transaction log can't be backed up. The recovery is exclusively based on the database back up. Then what is the point of having a transaction log for a database in this mode?
Could some of you point out what I have missed?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's the way SQL Server (and other RDBMS) are designed. Every operation is written to logged before it's actually done. This way you can manage transactions that will be atomic.

Someone else can go deeper or you can read the Stairway to Transaction Log Management in SQL Server[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

It's needed for a couple of small things like transaction rollbacks and crash recovery on restart (ensuring a consistent database)

If there was no transaction log, then any failure in any data modification, any explicit rollback, any uncontrolled shutdown would require that the database be restored from backup afterwards as there would be no other way to get back to a transactionally consistent state. Except that full backups need the transaction log to be transactionally consistent too.

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

hl6a Ten Centuries Points: 1004 More actions · Answer 3

Oh, you are definitely right - all the situations you mentioned need a transaction log to ensure consistency quickly. Thanks so much for your expert explanation!