Database Reference
In-Depth Information
Figure 29-12.
SIMPLE recovery model: Log truncation after CHECKPOINT
Thus, in the SIMPLE recovery model, the active part of transaction log starts with VLF, which contains the oldest
of LSN of the oldest active transaction or the last CHECKPOINT.
■
Note
an active database backup can defer transaction log truncation until it is completed.
As you can guess, even though SQL Server supports crash recovery in the SIMPLE model, you should keep both
data and log files intact to avoid data loss and to keep the database transactionally consistent.
Alternatively, with the FULL or BULK-LOGGED recovery models, SQL Server supports transaction log backups,
which allow you to recover the database and avoid data loss regardless of the state of the data files, as long as the
transaction log is intact. This assumes, of course, that a proper set of backups is available.
■
Note
We will talk about the backup and recovery process in greater detail in Chapter 30, “Designing a Backup Strategy.”
In the FULL and BULK-LOGGED recovery model, SQL Server requires you to perform transaction log backup
in order to trigger log truncation. Moreover, truncation can be delayed if you have other processes that need to read
the transaction log records. Think about Transactional Replication, Database Mirroring, and AlwaysOn Availability
Groups as examples of such processes.
Figure
29-13
shows one example. Both minimum and current LSNs are in VLF5, although the LSN of the last
transaction log backup is in VLF3. Therefore, the active portion of transaction log includes VLF3, VLF4, and VLF5.
