Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why do databases in Simple Recovery Mode need transaction log? Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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!
Post #1492208
Posted Friday, September 6, 2013 8:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492271
Posted Friday, September 6, 2013 8:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 42,836, Visits: 35,966
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 2008, MVP
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

Post #1492287
Posted Friday, September 6, 2013 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:14 PM
Points: 38, Visits: 140
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!
Post #1492336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse