SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why do databases in Simple Recovery Mode need transaction log?


Why do databases in Simple Recovery Mode need transaction log?

Author
Message
hl6a
hl6a
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 144
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!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40648 Visits: 19815
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


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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216619 Visits: 46278
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
hl6a
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 144
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search