October 30, 2002 at 8:27 pm
Books Online:
Write-Ahead Transaction Log
- Microsoft® SQL Server™ 2000, like many relational databases, uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record.
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 instead made to the copy of the page in the buffer cache. The modification is not written to disk until either the database is checkpointed, or the modifications 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 recording 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 were flushed before the log record, it would create a modification on disk that could not be rolled back if the server failed before the log record were written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.
------
My question is when a transaction is recorded in the log cache, shoudn't it immediately get written to the log file on disk?
What happens if a power outage occurs? All log cache transaction information will be lost.
October 31, 2002 at 8:31 am
According to Kalen Delaney's Inside Microsoft SQL Server 2000:
"A process never receives acknowledgement that a transaction has been committed unless it is on disk in the transaction log." (Emphasis added)
As she explains, before writing log records to the disk, they need to be formatted, and so there are sets of contiguous memory reserved, called "log caches," where the log records are maintained. When a transaction commits, the cache is placed into the "flushQueue" and the process that owns that log cache is put into a wait state until it is flushed to the disk. Once it has been flushed - written to disk - the process can resume.
So, what it boils down to is: your transaction will not complete until the log data is safely written to disk, thus ensuring the integrity of your transactions, even in the case of a power outage. If the system has told you that your transaction has completed, rest assured it is written to disk and recoverable.
Matthew Burr
October 31, 2002 at 11:38 am
The explanation above is the best, though it's possible some hardware controllers, like RAID cards, may have this cached even though the OS thinks it's written. Most controllers are write-through, but there is the possibility things are cached. A good reason to purchase the battery options on a controller.
Steve Jones
October 31, 2002 at 12:26 pm
how does an EMC SAN work with this?
I think we have 16GB of cache on the SAN here.
Write-through?
Write-back?
October 31, 2002 at 1:02 pm
Have to talk to EMC.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply