The transaction log is a serial record of all modifications
that have occurred in the database. SQL Server writes changes to the log before
it writes changes to the actual data file.
SQL Server maintains a buffer cache into which it reads data
pages when data must be retrieved. Data
modifications are not made directly to disk, but are made to the copy of the
page in the buffer cache. The modification is not written to disk until a
checkpoint occurs in the database, or the modification must be written to disk
so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called
flushing the page. A page modified in the cache, but not yet written to disk,
is called a dirty page.
At the time a modification is made to a
page in the buffer, a log record is built in the log cache that records the
modification. This log record must be written to disk before the associated
dirty page is flushed from the buffer cache to disk. If the dirty page is
flushed before the log record is written, the dirty page creates a modification
on the disk that cannot be rolled back if the server fails before the log
record is written to disk. SQL Server
has logic that prevents a dirty page from being flushed before the associated
log record is written. Log records are written to disk when the transactions
are committed.
When users change
data, SQL Server doesn't write that change directly to the data. Rather, SQL
Server locates the appropriate data and then loads it into a special area of
RAM called the data cache. Changes are made in RAM. Then, SQL Server copies
changes waiting in RAM to the transaction log. Only then does SQL Server write
changes to the actual data file.
This is called a write-ahead
log because SQL Server writes changes to the log before it writes changes
to the actual data file. This approach is quite a bit faster than writing
directly to the data file.
