Database Reference
In-Depth Information
Figure 29-10.
DBCC LOGINFO output
Database Recovery Models
There are three database recovery models that affect transaction log management and truncation behavior:
SIMPLE
,
FULL,
and
BULK-LOGGED
. While SQL Server logs enough information to roll back transactions and/or perform crash
recovery regardless of the recovery model, they control when a log is truncated and when VLFs become inactive. You
cannot access and redo any actions from the inactive part of the log, and therefore truncation affects the amount of
potential work loss if data files are unavailable.
It is again worth mentioning that transaction log truncation does not reduce the size of the log file, but rather it
marks VLFs as inactive and ready for reuse.
In the SIMPLE recovery model, SQL Server truncates the transaction log at CHECKPOINT. Let's assume that you
have a system with three active VLFs, as shown in Figure
29-11
. The oldest active LSN is in VLF4. Therefore, there is
the possibility that SQL Server will need to access log records from VLF4 and VLF5 in case of transaction rollbacks,
which require SQL Server to keep VLF4 and VLF5 active.
Figure 29-11.
SIMPLE recovery model: Initial stage
There are no log records from the active transactions in VLF3, although some of the dirty data pages in the buffer
pool may have corresponding log records stored there. SQL Server needs to access those records in case of a crash
recovery to be able to redo the changes; therefore VLF3 should also be kept active.
When SQL Server performs a CHECKPOINT, all of the dirty data pages are saved into the data file. As a result,
crash recovery does not need to redo any changes related to log records from VLF3, and it can be truncated and
marked as inactive. However, VLF4 must be kept active to support the rollback of the transactions, which have
corresponding log records stored in VLF4. Figure
29-12
illustrates this point.
