locked
Simple Recovery Mode RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Can someone settle an argument.

    Are fewer entries written to the transaction log when a database is in SIMPLE recovery mode?

    This is not about how the transaction log is truncated on each checkpoint, just does SQL write less to the transaction log.

    Just in case there is a difference I am talking about recent versions of SQL Server say 2008R2 onwards

    Thanks.

    Friday, August 30, 2013 3:56 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Are fewer entries written to the transaction log when a database is in SIMPLE recovery mode?

    Hello Brendan,

    No, the amount of logged transactions are always the same if the database is in recovery mode "simple" or "full", because they are always used if an error occurs and a rollback is necessary.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 30, 2013 4:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In the event of a minimally-logged operation such as bulk insert*, minimally-logged index rebuild, SELECT INTO* etc ( these are only possible in simple or bulk_logged recovery models ), then yes.  Otherwise no.

    * certain conditions must apply for the operation to be minimally logged

    Friday, August 30, 2013 4:15 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Are fewer entries written to the transaction log when a database is in SIMPLE recovery mode?

    Hello Brendan,

    No, the amount of logged transactions are always the same if the database is in recovery mode "simple" or "full", because they are always used if an error occurs and a rollback is necessary.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 30, 2013 4:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In the event of a minimally-logged operation such as bulk insert*, minimally-logged index rebuild, SELECT INTO* etc ( these are only possible in simple or bulk_logged recovery models ), then yes.  Otherwise no.

    * certain conditions must apply for the operation to be minimally logged

    Friday, August 30, 2013 4:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you that was my understanding.

    Friday, August 30, 2013 4:15 PM