Write-Ahead Transaction Log

Question

Post reply

Write-Ahead Transaction Log

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
October 30, 2002 at 8:27 pm

#64274

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mdburr Default port Points: 1483 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 2

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

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com