• Simple recovery does not truncate the log at every checkpoint. SQL will always, regardless of recovery mode, keep track of the oldest active transaction. The LRSN of the oldest transaction will relate to a logical log file (see BOL for what this means).

    When the current oldest active transaction ends, SQL establishes a new oldest LRSN. If the new LRSN is in a different logical log file to the previous LRSN, SQL releases the previous logical log file (and any intermediate logical log files) for further processing.

    Now comes the difference between Simple and Full recovery...

    If you are in Simple recovery, the released logical log files are put in the re-use list.

    If you are in Full or Bulk Logged recover, the released logical log files are put in a backup-pending status.

    Both of the above operations take next to no time to complete, so there is no processing overhead at this point between Simple, Bulk-logged and Full recovery.

    Next, if you are in Bulk-logged or Full recovery, at some point you will do a log backup. As each logical log file in pending status is backed up, it is reset to re-usable. The only extra processing overhead for using Full compared to Simple is the time taken to do the log backups. With Bulk-logged, you save some processing time compared to both Full and Simple when you are doing bulk insert operations - BOL has details of this.

    The log backup will process all logical log files that are in backup pending or in-use state. If you do multiple log backups during a time when the oldest LRSN has not changed, you will back up the active logical logs multiple times.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara