2

I'm studying for the 70-432 (SQL Server 2008) exam, and I'm a bit confused about how the transaction log works.

From what I understand, (correct me if I'm wrong...) the log is actively stored in memory and copied to a file on the drive as often as possible. At every checkpoint, complete transactions are committed to the data file on the hard drive (that is, they're not actually written to the drive at all until the checkpoint. And the tail always holds the transactions that have not been committed yet.

When a disaster happens, and you try to backup the "tail" of the log. Is that stored in a file, or are you trying to dump from memory? I'm aware that this is not always possible to do, but it's highly advisable to try to do it.

Am I right in this understanding?

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
Mirrana
  • 197
  • 2
  • 10

2 Answers2

3

From what I understand, (correct me if I'm wrong...) the log is actively stored in memory and copied to a file on the drive as often as possible.

Transactions are always hardened to disk in the transaction log before they are considered to have been committed to the database. The buffer cache, on the other hand, does live in memory and reflects the current state of the database after transactions have been committed and hardened in the transaction log. The buffer cache is flushed to disk during a checkpoint.

Since it lives in RAM, a system failure could cause information that was stored in the buffer cache (and not yet flushed to disk) to be lost. Since everything that was in the buffer cache was stored in the transaction log, SQL is able to recover all of the transactions that were committed but not yet flushed to the data files at the point of failure.

Chris McKeown
  • 7,168
  • 1
  • 18
  • 26
-1

Have a read here: http://msdn.microsoft.com/en-us/library/ms345419(SQL.105).aspx

MichelZ
  • 11,068
  • 4
  • 32
  • 59
  • 1
    We really do prefer that answers have content, not pointers to content. This ensures that the answer will remain available even if the link goes dead. Whilst this may theoretically answer the question, [it would be preferable](http://meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – user9517 May 29 '12 at 10:14
  • 1
    I know, however the essentials in the link would be veeeeery big. i'd rather post nothing then... – MichelZ May 29 '12 at 10:35