Database Reference
In-Depth Information
Listing 29-1.
Transaction with delayed durability
begin tran
/* Do something */
commit with (delayed_durability=on)
Any other SQL Server technologies that work with the transaction log would see and process commit records
from transactions with delayed durability only after those records were hardened in the log and, therefore become
durable in the database. For example, if a database backup finishes in between a transaction commit and log buffer
flush, the commit log record would not be included in the backup and, therefore, the transaction would be rolled back
at the time of a restore.
Another example is AlwaysOn Availability Groups. Secondary nodes will receive commit records only after those
records are hardened in the log on the primary node and transmitted over network.
■
We will discuss database backup and restore processes in detail in Chapter 30, “Designing a Backup Strategy,”
and about alwayson availability groups in Chapter 31, “Designing a high availability Strategy.”
Note
Virtual Log Files
Even though a transaction log can have multiple files, SQL Server works with it in a sequential manner while writing
and reading a stream of log records. As a result, SQL Server does not benefit from the multiple physical log files.
Internally, SQL Server divides every physical log file into smaller sections called
Virtual Log Files (VLF)
. SQL
Server uses virtual log files as a unit of management, which can be either active or inactive.
A VLF is active when it stores the
active portion
of the transaction log
,
which contains the stream of log records
required to keep the database transactionally consistent in the event of a transaction rollback or unexpected SQL
Server shutdown. For now, do not focus on what keeps log active; we will examine this later in the chapter. An inactive
VLF contains the
truncated
(inactive) and unused parts of the transaction log.
Figure
29-8
shows an example of a transaction log and virtual log files.
Figure 29-8.
Transaction Log and Virtual Log Files
