Database Reference
In-Depth Information
SQL Server uses
Write-Ahead Logging
, which guarantees that log records are always written to the log file before
dirty data pages are saved to the database. In Chapter 1, I mentioned that log records are saved synchronously with data
modifications, while data pages are saved asynchronously during the CHECKPOINT process. That is not 100 percent
accurate, however. SQL Server caches log records in the small (about 60KB per database) memory cache called
Log Buffer
saving multiple log records at once. This helps reduce the number of physical I/O operations required.
Now let's look at how data modifications work in greater detail. Let's assume that we have a system with an
empty Log Buffer and the last LSN of 7213 in the transaction log, as shown in Figure
29-1
. Let's also assume that there
are two active transactions: T1 and T2. Each of those transactions has BEGIN TRAN log records already saved in the
transaction log.
Figure 29-1.
Data modifications: Initial State
As a first step, let's assume that we have transaction T1, which updates one of the rows from page (1:24312).
As you can see in Figure
29-2
, this operation generates a new log record, which has been placed into the Log Buffer.
In addition, it modifies data page marking it as dirty, updating the LSN in the page header, and changing the data row.
Even though the log record has not been saved (
hardened
) to the log file, it is not critical as long as the data page has
not been saved in the data file. Both log record and modifications on the data page will be gone in case of a SQL Server
crash, which is fine because the transaction has not been committed.
